Forum Discussion
Field parameters date issue
- 11 months ago
Hi maverickf17 ,
Let me start by the easy part the question is the context of the switch measure the calculations inside the switch are done in order and if it is true then it's stops and returns that calculation.
In your case you have the first value to be check is the ISINSCOPE ( 'Calendar'[Date].[Year] ) since the year is part of your table the result will always fall back to this one so giving you previous year value:
If you redo the order like this:
Prev Test = SWITCH ( TRUE (), ISINSCOPE ( 'Calendar'[Date].[Day] ), CALCULATE ( [All Card Transactions], DATEADD ( VALUES ( 'Calendar'[Date] ), -1, DAY ) ), ISINSCOPE ( 'Calendar'[Date].[Month] ), CALCULATE ( [All Card Transactions], DATEADD ( VALUES ( 'Calendar'[Date] ), -1, MONTH ) ), ISINSCOPE ( 'Calendar'[Date].[Quarter] ), CALCULATE ( [All Card Transactions], DATEADD ( VALUES ( 'Calendar'[Date] ), -1, QUARTER ) ), ISINSCOPE ( 'Calendar'[Date].[Year] ), CALCULATE ( [All Card Transactions], DATEADD ( VALUES ( 'Calendar'[Date] ), -1, YEAR ) ) )Starting on the lowest value day then you get the expected result:
However I would advise you to be carefull with the auto date-time option since the best practice for the usage of time intelligence calculations is to turn off auto date-time and create a full calendar table in your model, and also mark as a date table.
If you have adjust your calendar table to the format below and then redo your parameter table and the calculation like the formula below you should get expected result:
Date param_ = { ("Quarter", NAMEOF('Calendar'[Quarter]), 0), ("Month", NAMEOF('Calendar'[Month]), 1), ("Day", NAMEOF('Calendar'[Day]), 2) } Prev Test = SWITCH ( TRUE (), ISINSCOPE ( 'Calendar'[Day] ), CALCULATE ( [All Card Transactions], DATEADD ( VALUES ( 'Calendar'[Date] ), -1, DAY ) ), ISINSCOPE ( 'Calendar'[Month] ), CALCULATE ( [All Card Transactions], DATEADD ( VALUES ( 'Calendar'[Date] ), -1, MONTH ) ), ISINSCOPE ( 'Calendar'[Quarter] ), CALCULATE ( [All Card Transactions], DATEADD ( VALUES ( 'Calendar'[Date] ), -1, QUARTER ) ), ISINSCOPE ( 'Calendar'[Year] ), CALCULATE ( [All Card Transactions], DATEADD ( VALUES ( 'Calendar'[Date] ), -1, YEAR ) ) )Once again the order of the calculations is very important.
You can always create you own date hierarchy using the right click on the column of the year and then
add the other columns also
Check some links about this subject but there are a lot more
https://www.sqlbi.com/articles/automatic-time-intelligence-in-power-bi/
https://data-mozart.com/tiq-part-1-how-to-destroy-your-power-bi-model-with-auto-date-time/
https://www.sqlbi.com/articles/mark-as-date-table/
Please see file attach.
Hi maverickf17 ,
Thanks for reaching out to the Microsoft fabric community forum.
Thanks for your prompt response
I wanted to follow up and confirm whether you’ve had the opportunity to review the information provided by MFelix If you have any questions or need further clarification, please don’t hesitate to reach out.
We truly appreciate your continued engagement and thank you for being an active and valued member of the community.
We look forward to hearing from you.
Best regards,
Lakshmi