Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a date table joined to my fact table. I'm trying to create a previous month measure in my tabular model. When I use the following formula referencing my date table, I get the exact same measure as the current month:
Measure 1:=CALCULATE([OTD to CRD], DATEADD(DimDate[Date], -1, month))
When I use the date filed in my fact table, I get the previous month value, however I'm not able to use the measure with any dimension or slicer. I get error "DATEADD only works with contiguous date selections:
Measure 1:=CALCULATE([OTD to CRD], DATEADD(Global[RequestDate], -1, month))
Also, when I try to create a MTD measure using the Date table, it returns a (blank) value. Works well when I use date field in my fact table
Measure 2:=CALCULATE([OTD to CRD], DATESMTD(DimDate[Date]))
What am I missing?
@webward3 wrote:
I have a date table joined to my fact table. I'm trying to create a previous month measure in my tabular model. When I use the following formula referencing my date table, I get the exact same measure as the current month:
Measure 1:=CALCULATE([OTD to CRD], DATEADD(DimDate[Date], -1, month))
When I use the date filed in my fact table, I get the previous month value, however I'm not able to use the measure with any dimension or slicer. I get error "DATEADD only works with contiguous date selections:
Measure 1:=CALCULATE([OTD to CRD], DATEADD(Global[RequestDate], -1, month))
Also, when I try to create a MTD measure using the Date table, it returns a (blank) value. Works well when I use date field in my fact table
Measure 2:=CALCULATE([OTD to CRD], DATESMTD(DimDate[Date]))
What am I missing?
Could you try
Measure 1 = CALCULATE([OTD to CRD], PREVIOUSMONTH(dimdate[Date]))
@webward3 wrote:
@Eric_ZhangI have blank value for my measure when taking that approach.
Could you share any sample data? Even better you can share the pbix file. You can upload it to any network drive and share the link. Do mask sensitive data before uploading.
Using your Measure 2
Create this Measure
Previous Month Measure = CALCULATE ( [Measure 2], DATEADD ( DATESMTD ( DimDate[Date] ), -1, MONTH ) )
or this should also work
Previous Month Measure = CALCULATE ( [Measure 2], PARALLELPERIOD ( DimDate[Date], -1, MONTH ) )
What is the range of your Calendar Table?
Thanks Sean but still getting same results.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |