Forum Discussion
Measure is not working
- 11 months ago
Boopep , Qtr and Qtr month comparision should be 3 month behind. Prefer to use a date table joined with date of start/end Date of you data table
M1= COUNT('Claims Profile 2025'[SERIES])
M1 Lag 3 = calculate([M1], dateadd(Date[Date], -3, Month) )
If you want to switch between month and qtr, you can use isinscopedifferent example, use dateadd in place of offset
Previouse Value = SWITCH(TRUE(),
ISINSCOPE('Date'[Date]), CALCULATE([Net], OFFSET(-1, ALL('Date'[Date]))),
ISINSCOPE('Date'[Month Year]), CALCULATE([Net],OFFSET(-1, ALL('Date'[Month Year sort],'Date'[Month Year],'Date'[Year],'Date'[Qtr Year]),ORDERBY('Date'[Month Year sort]))),
ISINSCOPE('Date'[Qtr Year]),CALCULATE([Net], OFFSET(-1, ALL('Date'[Year], 'Date'[Qtr Year]))),
ISINSCOPE('Date'[Year]), CALCULATE([Net],OFFSET(-1, ALL('Date'[Year])))
)
More time intelligence functions
https://youtu.be/cyWVzAQF9YU?t=41167 - 11 months ago
Hi Boopep ,
Thank you for reaching out to the Fabric Community.
I’ve tested your scenario with sample data, and the logic matches your requirements. I’ve attached a PBIX file showing the setup and expected behavior, please review it and let me know if you need any changes or improvements.
FYI:Thank you for your response amitchandak .
Regards,
Yugandhar.
Boopep , Qtr and Qtr month comparision should be 3 month behind. Prefer to use a date table joined with date of start/end Date of you data table
M1= COUNT('Claims Profile 2025'[SERIES])
M1 Lag 3 = calculate([M1], dateadd(Date[Date], -3, Month) )
If you want to switch between month and qtr, you can use isinscope
different example, use dateadd in place of offset
Previouse Value = SWITCH(TRUE(),
ISINSCOPE('Date'[Date]), CALCULATE([Net], OFFSET(-1, ALL('Date'[Date]))),
ISINSCOPE('Date'[Month Year]), CALCULATE([Net],OFFSET(-1, ALL('Date'[Month Year sort],'Date'[Month Year],'Date'[Year],'Date'[Qtr Year]),ORDERBY('Date'[Month Year sort]))),
ISINSCOPE('Date'[Qtr Year]),CALCULATE([Net], OFFSET(-1, ALL('Date'[Year], 'Date'[Qtr Year]))),
ISINSCOPE('Date'[Year]), CALCULATE([Net],OFFSET(-1, ALL('Date'[Year])))
)
More time intelligence functions
https://youtu.be/cyWVzAQF9YU?t=41167