Forum Discussion

epang's avatar
epang
Advocate I
7 days ago

Help with Date intelligence

Hi team,   I have created Total cases for the Current MTD , Current QTD and Current YTD. The data shows correctly However, when I create previous MTD, previous QTD and previous YTD. The data are a...
Need Help
Tips and Tricks
epang's avatar
epang
Advocate I
7 days ago

 

DimDate =
VAR StartDate = DATE(2020,1,1)
VAR EndDate = DATE(2030,12,31)

RETURN
ADDCOLUMNS(
    CALENDAR(StartDate, EndDate),

    "Year", YEAR([Date]),
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMMM"),
    "Month Short", FORMAT([Date], "MMM"),

    "Quarter Number", QUARTER([Date]),
    "Quarter", "Q" & QUARTER([Date]),

    "Year-Month", FORMAT([Date], "YYYY-MM"),
    "Year-Quarter", YEAR([Date]) & "-Q" & QUARTER([Date]),

    "Day Number", DAY([Date]),
    "Day Name", FORMAT([Date], "DDDD"),
    "Day Short", FORMAT([Date], "DDD"),

    "Week Number", WEEKNUM([Date])
)
 
 
i have created the following dim date table. After that, when I join the date to lodge date, the relationship becomes inactive. Any clues to fix that?
 
 
  • epang's avatar
    epang
    Advocate I
    7 days ago

    After I create the table and join the date table field and the date in the fact table, the relationship is now active, but when i use the suggested formula to create previous Month to Date, it returns as blank.  

     

    Previous MTD = CALCULATE([Total Cases], DATESMTD(DATEADD(DimDate[Date],-1,MONTH)))
     
    Any clues to fix that?? I suspect it may be due to the format of the date. for example, the lodged date is recorded as 1/7/2026 11:03am. the date field is 1/7/202612:00am. will that cause power bi failure to display the data at all? Thanks
    • ShahRukhSameer's avatar
      ShahRukhSameer
      Advocate III
      7 days ago

      Hi epang,

       

      Yes, the time portion could be causing the issue here.

       

      Your DimDate[Date] has something like 01/07/2026 12:00 AM, while the fact table has 01/07/2026 11:03 AM. Although they are the same calendar date, Power BI treats them as different DateTime values.

       

      I would create a date-only column in the fact table and use that for the relationship:

       

      Lodged Date Only =
      DATEVALUE(FactCases[Lodged Date])

       

      Then create the relationship:

      DimDate[Date] → FactCases[Lodged Date Only]

      with DimDate on the 1 side and the fact table on the many side.

       

      Also make sure DimDate[Date] is set to the Date data type and mark DimDate as the Date table.

       

      Your Previous MTD measure can then be:

      Previous MTD =
      CALCULATE(
      [Total Cases],
      DATEADD(DimDate[Date], -1, MONTH)
      )

       

      One other thing I'd check is what you're using in the slicer or visual. Ideally, use DimDate[Date] rather than FactCases[Lodged Date] for your date filtering.

       

      For example, if you're looking at July 1–15, the Previous MTD should return June 1–15.

       

      If it is still blank after removing the time portion, I'd check whether there are any other relationships between the fact table and DimDate, such as Created Date, Closed Date, etc. Multiple date relationships can sometimes cause one of them to be inactive.

       

      So I'd start by creating the date-only column and testing the measure again. That is the first thing I'd check in this case.