Forum Discussion
Unable to get correct values using Time Intelligence functions
Hi Fam,
I need to calculate YTD, QTD & MTD sales depending on the date selection done in the slicer. Also, I want to do calculations for the exact same time frame selected in the slicer.
Steps that I have implemented:- 1) Created Calendar Table and established relationship with Fact Table as 1 to Many.
2) Used DATESYTD, DATESQTD & DATESMTD to calc the current year values.
3) For previous year DAX used - CALCULATE(SUM(SALES), SAMEPERIODLASTYEAR(DATESYTD(CALENDAR(Date)))).
Still I am not getting correct values.
Please help.
9 Replies
Hello Nidhi
- checks if the relationship that exists has the columns in the same date format,
- you are using hours in the date?
- share an example and the format of the relationship columns
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BCThe date format is the same for both the columns.
Hi again,
need some example to analize
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC
Hi again,
At least can you share your measure?
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BCPY Sales --> CALCULATE(SUM(SALES), SAMEPERIODLASTYEAR(DATESYTD(CALENDAR(Date)))).
CY Sales --> CALCULATE(SUM(SALES), DATESYTD(CALENDAR(Date)))
Hi, NidhiBhusari
You can check my article to get the correct calculations.
https://allure-analytics.com/index.php/2022/05/24/time-intelligence-in-power-bi/
In any case try to follow this approach:
1) Create YTD = CALCULATE(SUM(SALES), DATESYTD(CALENDAR(Date))
2) Create PYTD = CALCULATE([YTD], SAMEPERIODLASTYEAR(CALENDAR(Date)) --YTD is the upper measure
Hi ALLUREAN , Thanks for the article.
But my query is I want to do calculations for the previous year of the exact same time frame selected in the slicer.Hi again Nidhi
Try:
PY Sales --> CALCULATE(SUM(SALES), SAMEPERIODLASTYEAR(CALENDAR(Date)))
note: use the column year in the visual
CY Sales --> CALCULATE(SUM(SALES), DATESYTD(CALENDAR(Date)))another solution is to use calcualtion groups, if you have lots of measures it will be the best way:
https://www.youtube.com/watch?v=IH4bcHhxunw
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC
