Forum Discussion

jaryszek's avatar
jaryszek
Icon for Super User rankSuper User
9 months ago
Solved

Date Slicer Returns Blank Results When Using Dim_Date to Fact Monthly Costs

Hello everyone, I’m working with a model where Dim_Date contains a daily date column (e.g., 01-03-2025). My fact table (Monthly_Costs) is connected to Dim_Date using a 1-to-many relationship (D...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    9 months ago

    jaryszek 

     

    Updated: Download example PBIX file

     

    Add a column to your date table that is the first of the month for each date e.g.

     

     

    Then use that First Date column in the date slicer.  It will work fine with your month slicer, which is presumably getting the month from the same date table.

     

     

     

     

     

     

     

    Phil

jaryszek's avatar
jaryszek
Icon for Super User rankSuper User
9 months ago

They have different names. One is Fact_Monthly so here I have aggregated data and second is Daily granularity, which is Fact_Daily. 

I can not aggregate them because they are describing different granularity within model.

Best,
Jacek

raisurrahman's avatar
raisurrahman
Icon for Helper II rankHelper II
9 months ago

Here two path you can go:


Option 1:

If you have two fact tables with different granularities and you are not comparing the two facts, you can use two separate date tables—one at monthly granularity and another at daily granularity.


Option 2:

If you need to compare the two facts, then aggregate your daily fact table using a GROUP BY to the monthly level and use a single monthly date table for both.



  • jaryszek's avatar
    jaryszek
    Icon for Super User rankSuper User
    9 months ago

    Thanks. 

    With option 1 would you connect dim_month with dim_date as 1 to many ?

    Best,
    Jacek