Forum Discussion
Need help with DAX
DiKi-I Hey,
you can follow this blog post - Unique Time Intelligence Analysis Examples for Pow... - Microsoft Fabric CommunityI hope this will fulfill your requirement.
Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
Hi DiKi-I,
Thank you for reaching out to the Microsoft Fabric Forum Community.
I will suggest to have separate date table in the data model for better results.
Try this dax measures.
FYTD Sales =
CALCULATE(
SUM('FactTable'[Sales]),
FILTER(
ALL('FactTable'),
'FactTable'[FiscalYear] = MAX('FactTable'[FiscalYear])
&& 'FactTable'[Date] <= MAX('FactTable'[Date])
)
)
Sales Last 3 Periods =
VAR CurrentFY = MAX('FactTable'[FiscalYear])
VAR CurrentFP = MAX('FactTable'[FiscalPeriod])
RETURN
CALCULATE(
SUM('FactTable'[Sales]),
FILTER(
ALL('FactTable'),
'FactTable'[FiscalYear] = CurrentFY &&
'FactTable'[FiscalPeriod] >= CurrentFP - 2 &&
'FactTable'[FiscalPeriod] <= CurrentFP
)
)
Sales Last 3 Months =
VAR MaxDate = MAX('FactTable'[Date])
VAR FromDate = EDATE(MaxDate, -2)
RETURN
CALCULATE(
SUM('FactTable'[Sales]),
FILTER(
ALL('FactTable'),
'FactTable'[Date] >= FromDate &&
'FactTable'[Date] <= MaxDate
)
)
Best regards,
Prasanna Kumar
my sap period has data like this 2015001, 2015002..... 2015012 .
How I can move back to last 2 period, suppose I'm on 2025001 which is max period. What would be the dax to fetch 2025001, 2024012, and 2024011
convert your SAP periods into usable dates
2015001 would need to be mapped to 2015-01-01 etc.
Then you can use EDATE([Date],-2)
