Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have 3 tables in my model, Accounting period, Calendar and Sales.
Accounting period records the start and end date of each fiscal period. Calendar is generated nolmal calendar. Sales records every sales invoice date, customer, and amount.
Now I have slicer to select one period and one date in the period. How could I calculate sum of sales invoice amount for same period last year? For example I select year 2020, period 1, date 2020/2/5 which is the 3rd day of period 1, I want to calculate sum of invoice amount of year 2019 period 1 first 3 days (which is 2019/2/4 to 2019/2/6).
Solved! Go to Solution.
@Anonymous , See like these are non standard period
You need rank on period YYYYPP
New columns
Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)
Period Day = datediff([Period Start Date], [Date], Day)+1
measure
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))
TD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank]) && [Period Day] <=max([Period Day])))
LPTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1 && [Period Day] <=max([Period Day])))
@Anonymous , See like these are non standard period
You need rank on period YYYYPP
New columns
Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)
Period Day = datediff([Period Start Date], [Date], Day)+1
measure
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))
TD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank]) && [Period Day] <=max([Period Day])))
LPTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1 && [Period Day] <=max([Period Day])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
88 | |
75 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |