Power BI 导入，建模和报表设置（DAX函数 M语言）

最新推荐文章于 2024-05-14 16:00:30 发布

weixin_43355344

最新推荐文章于 2024-05-14 16:00:30 发布

阅读量909

点赞数 23

文章标签：数据分析

本文链接：https://blog.csdn.net/weixin_43355344/article/details/135979437

版权

一：原通过文件夹导入（增加新文件，刷新后数据自动加载，无需手动维护）

以下展示案例用的表格的【列，行】

二：基于原始数据，利用SUMMERRIZE生成辅助表

辅助分类表

ocataloge = SUMMARIZE('sales',sales[分类])

辅助产品表

oclass = SUMMARIZE( 'sales',sales[产品线],sales[产品名])

辅助公司表

ocompany = SUMMARIZE( 'sales',sales[公司代码],sales[公司名])

辅助日期表

odate = SUMMARIZE( 'sales',sales[月],sales[月].[季度],sales[月].[月份])

销售主表

osales = SUMMARIZE( 'sales',sales[产品线],sales[公司代码],sales[分类],sales[月],sales[金额])

新增列【更新日期】

从文件名【table_20240205_1.xlsx】提取【20240205】，新建列，输入公式

= Text.Middle([Source.Name], 6, 8)

新增列【索引】

从文件名【table_20240205_1.xlsx】提取【1】，新建列，输入以M语言

let
    FileName = [Source.Name],
    SplitList = Text.Split(FileName, "_"),
    LastPart = List.Last(SplitList),
    Number = Text.Start(LastPart, Text.Length(LastPart) - 5)
in
    Number

每一个文件存有当月的最新销售金额，需求通过月金额，计算单日金额

原数据：

table_20230401_1.xlsx 4月金额 5元

table_20230402_2.xlsx 4月金额 12元

table_20230402_2.xlsx 4月金额 17元

计算后数据：

table_20230401_1.xlsx 4月金额 15元 4月1日 15元

table_20230402_2.xlsx 4月金额 26元 4月2日 11元

table_20230403_3.xlsx 4月金额 39元 4月3日 13元

计算逻辑：当天的销售金额 =当天月金额 - 前一天月金额

新增列【前日金额】

文件名格式 table_yyyyddmm_1.xlsx

实际业务：yyyyddmm为取数据的日期，因法定休假等，日期存在不连续性，如工作日2024/02/05，2024/02/07，为实现当天销售和前一天销售相减，可以通过设置索引的方法，实现销售数据的连续性，为达此效果，文件格式的末尾添加自增数， table_20240205_1.xlsx，table_20240207_2.xlsx

具体DAX函数

前日金额 = 
VAR cIndex = odyapsi[索引]
VAR cCompany = odyapsi[公司代码]
VAR cProduct = odyapsi[产品线]
VAR cMonth = odyapsi[月份]
VAR cCat = odyapsi[分类]
VAR cOgnazation = odyapsi[销售组织]
VAR preAmount = LOOKUPVALUE(
    odyapsi[金额],
    odyapsi[索引], cIndex - 1,
    odyapsi[公司代码], cCompany,
    odyapsi[产品线], cProduct,
    odyapsi[月份], cMonth,
    odyapsi[分类], cCat,
    odyapsi[销售组织], cOgnazation
)
RETURN
IF(ISBLANK(preAmount), BLANK(), preAmount)

增加列【当日金额】

第一个文件索引为1，默认销售金额就是单日销售金额

当日金额 = IF([索引]=1,[金额],if(ISBLANK([前日金额]),0 , [金额]-[前日金额]))

实现效果：

创建度量值【月】

销售金额合计，以此类推计算【1月】-【12月】

1月 = SUMX(
    FILTER(
        odyapsi,
        YEAR(odyapsi[月份]) = 2024 && MONTH(odyapsi[月份]) = 1 && odyapsi[索引] = MAX(odyapsi[索引])
    ),
    odyapsi[金额]
)

创建度量【季度】

销售金额合计，以此类推计算【1季度】-【4季度】，季度根据公司财年设定

1Q = SUMX(
    FILTER(
        odyapsi,
        YEAR(odyapsi[月份]) = 2023 && MONTH(odyapsi[月份]) >= 4 && MONTH(odyapsi[月份]) <= 6 &&odyapsi[索引] = MAX(odyapsi[索引])
    ),
    odyapsi[金额]
)

创建度量【半财年】

销售金额合计，以此类推计算【上半财年】-【下半财年】

H1 = odyapsi[1Q] + odyapsi[2Q]

创建度量【全年】

销售金额合计

Total = odyapsi[H1] + odyapsi[H2]

以分类单独求金额合计，本案列，分类的值【Sales，Inventory，Purchase】

创建度量【Sales】

分类为Sales的销售金额合计

S实绩 = CALCULATE(SUM(odyapsi[金额]),odyapsi[分类]="Sales",odyapsi[索引]=max(odyapsi[索引]))

创建度量【期末库存】

期末库存 = 
VAR CurrentDate = TODAY()

VAR MaxInventoryDate = CALCULATE(
                            MAX(odyapsi[月份]),
                            odyapsi[分类] = "Inventory",
                            odyapsi[索引] = MAX(odyapsi[索引])
                        )
VAR currentInventory  = CALCULATE(
                            SUM(odyapsi[金额]),
                            odyapsi[分类]="Inventory",
                            odyapsi[月份] = MaxInventoryDate,
                            odyapsi[索引] = MAX(odyapsi[索引])
                        )
RETURN currentInventory

创建度量【平均3个月销售】

平均3个月销售 = 
VAR CurrentDate = TODAY()
VAR StartDate = EDATE(CurrentDate, -3)
VAR EndDate = EOMONTH(EDATE(CurrentDate, -1), 0)
RETURN
AVERAGEX(
    FILTER(
        SUMMARIZE(
            odyapsi,
            odyapsi[月份],
            "MonthlySales", CALCULATE(SUM(odyapsi[金额]), odyapsi[分类] = "Sales",odyapsi[索引]=MAX(odyapsi[索引]))
        ),
        odyapsi[月份] >= StartDate && odyapsi[月份] <= EndDate 
    ),
    [MonthlySales]
)

创建度量【库存销售比】

库存销售比 = 
 
IF(
    odyapsi[平均3个月销售] > 0,
    odyapsi[期末库存]  / odyapsi[平均3个月销售],
    BLANK()
)

创建度量【是否最新日期】

用于高亮显示最新日期的销售金额

是最新日期 = 
IF(MAX(odyapsi[更新日期]) = CALCULATE(MAX(odyapsi[更新日期]), ALL(odyapsi)), BLANK(), 1)

创建度量【销售目标】

销售目标 = CALCULATE(SUM(targetsbu[金额]),ALLSELECTED(targetsbu[产品线]))

创建度量【前一个月销售合计】

s1月 = SUMX(
    FILTER(
        odyapsi,
        YEAR(odyapsi[月份]) = 2024 && MONTH(odyapsi[月份]) = 1 && 
        RANKX(
            FILTER(odyapsi, YEAR(odyapsi[月份]) = 2024 && MONTH(odyapsi[月份]) = 1), 
            odyapsi[索引], 
            , 
            DESC, 
            DENSE
        ) = 2
    ),
    odyapsi[金额]
)