DAX Time Intelligence

[SVM9698]

Hi All,

I have a question related to Month To Date,Quater To Date and YearTo Date dax formulas , in my current project we are dealing with data which has only month numbers related to date information in fact table .With only month number is it possible to calculate Month To Date,Quater To Date and Year To Date dax as there no proper materials on web related to it, i have create custom date table in power query and created relationship with fact table but its still no use.

Thanks in advance and regards!

[JoeBarry]

Hi @SVM9698 

To use these functions, you will need to use the Date Table you created https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

Does the Fact table have a relationship with other Table on the Month ID? Is the Month ID 1-12 or is it a YearMonth column? 202301 for example?

Don't forget to convert your date table to a date table. 

Can you provide a screenshot of the month column from the other table? Then i can help you further

Thanks

Joe

[SVM9698]

 Thanks a lot for your input @JoeBarry 

The issue here is in my fact table(Volume table) there is only month number and no other date information like date,year etc only month number(1 to 12) that's it, even though considering current month is september i.e 9. This fact table contains information of oct to dec as well. I have created custom date table and have 1 to many relationship only to fact table. This custom table i dont have continous dates so cant convert into date table, i will try keeping bridge table then convert into date table.

How can we create current month if we have all months?

How can we create MTD,QTD and YTD filters on this type of data?

Thanks in advance and regards.

[JoeBarry · Accepted solution]

Does the Volume table only have data from a certain year or is there many years in it? If it is just for one year and it's the current year, then in power query add a custom column called Date to the volume table. 

"01/" & [Periodo 445] & "/2023"

Convert to Date.

Create a new date table 

Date Table = = CALENDAR (MINX (Volume, [Date]), MAXX (Volume, [Date]))

You can other columns using the FORMAT Function. This will give you all dates between the Min and Max dates of your Volume table.

Create the relationship as above. One to many on the date column in the date table to the date column in the Volume table. 

Then you can create the Time Intelligence measures

MTD = TOTALMTD(SUM(Volume[Amount]), 'DateTable'[Date])

Thanks

Joe