Forum Discussion

SVM9698's avatar
SVM9698
Regular Visitor
2 years ago
Solved

DAX Time Intelligence

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 inform...
Data Modeling
  • JoeBarry's avatar
    JoeBarry
    2 years ago

    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

JoeBarry's avatar
JoeBarry
Solution Sage
2 years ago

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's avatar
SVM9698
Regular Visitor
2 years ago

 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's avatar
    JoeBarry
    Solution Sage
    2 years ago

    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