Need help with DAX

[DiKi-I]

hi,

I'm bringing data from sap using direct query. I have view which already has fiscal year and fiscal period. The fiscal year follows sap calendar and there fiscal year starts on 2nd thursday of may or 3 thursday. I'm not fully sure but it follows sap calendar and my table already has calendar date, fiscal year and period as whole number. I need help how i should create time intelligence calculation like fytd, last 3 months and so. Also since its is in direct query, I'm not able to creatand calculated column on the table.

[v-pgoloju]

Hi @DiKi-I,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @DiKi-I,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[HarishKM · Accepted solution]

@DiKi-I Hey,
you can follow this blog post - Unique Time Intelligence Analysis Examples for Pow... - Microsoft Fabric Community

I 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

[v-pgoloju]

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

[DiKi-I]

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

[lbendlin]

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)

[DiKi-I]

Issue is these are custom sap fiscal period of 4 weeks , 2025001 starts at around 2nd week of may. I would need to write custom calculations.

[lbendlin]

In that case you can use the WINDOW or OFFSET function to go back x periods.

WINDOW(-2,REL,0,REL,...)

[v-pgoloju]

Hi @DiKi-I,

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[lbendlin]

Does your semantic model have a calendar table that is clearly reflecting these custom fiscal periods?  You will need to create your own time intelligence measures.

[DiKi-I]

I don't have any calendar table. The fact table coming from source has fiscal year, period , week, date . Basically its a flat table from the source.

[lbendlin]

Sorry to hear that. Performance will be horrible at best.  Do your SAP queries fold?

[DiKi-I]

I tested it and can see native queries so I believe it is folding.