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 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!

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

3 Replies

  • 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