Forum Discussion
Time Intelligence Calculation Not working
Hi,
I have two tables One Fact table and one date table which are mapped based on DATE columns from both tables.
I am trying to calculate Count ID from Fact Table for TOTALYTD, TOTALQTD, TOTALMTD with respect to Date column from DATE table but its not working.
The formula I am using is: TOTALYTD(DISTINCTCOUNT(ID), DATE[DATE]) - Its not working giving me NUL value
However if i use the date column from Fact Table its working,
TOTALYTD(DISTINCTCOUNT(ID), FACTTABLE[DATE]) - This is working.
Why its not working when I use DATE[DATE]?
The relationship betweeen two tables is One (Date) to Many (Fact) with BOTH CROSS FILTER DIRECTION.
Any suggestion?
Thanks
hi damit23183
When you create visual, you need to use all the "date" field from DATE table, do not use any "date" field from fact table.
https://radacad.com/do-you-need-a-date-dimension
if you still have the problem, please share your sample pbix file, that will be a great help.
Regards,
Lin
5 Replies
Try using DATESYTD with DateDim Table Column and Let me know
Hi Vijay,
I tried;
CALCULATE(DISTINCTCOUNT(ID), DATESYTD(Date[DATE]))
It didnt work as well.
Thanks
damit23183 , There can be a few reasons. Make sure you use a date table that marked as Date.
Now when the page does not have a date filter/slicer. From where these time intelligence function will take the end date(these are end date function, there few which takes start date).
That is the last date of date passed FACTTABLE[DATE] or DATE[DATE]
In this case end date will be the end date or calendar (Date) or end date of the table (Facttable).
Make sure you use date slicer or stop your calendar till today
refer
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9Hi Amit,
I went through your all articles and used with and without Time Intelligence logic but did not work with DATE table.
My question is: Is that Ok if I use date from Fact Table while creating any measures and then apply slicer from DATE table.
Just wondering what is difference between using date column from Fact Table and Date Table in measures.
Thanks
hi damit23183
When you create visual, you need to use all the "date" field from DATE table, do not use any "date" field from fact table.
https://radacad.com/do-you-need-a-date-dimension
if you still have the problem, please share your sample pbix file, that will be a great help.
Regards,
Lin
