Time Intelligence Calculation Not working

[damit23183]

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

[amitchandak]

@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-La...
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

[damit23183]

Hi 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

[v-lili6-msft · Accepted solution]

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

[VijayP]

@damit23183 

Try using DATESYTD with DateDim Table Column and Let me know

[damit23183]

Hi Vijay,

I tried;

CALCULATE(DISTINCTCOUNT(ID), DATESYTD(Date[DATE]))

It didnt work as well.

Thanks