Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
dw2468
Frequent Visitor

Calculate Function with DATEADD and Filter not working

I have the code for current month, which works well for me. However if I check the DATEADD to -1, the current month row data doesn't show the previous month value in the table visual. Any idea why?

 

 

CurrM = CALCULATE(
    SUM('CADUSD'[Balance]),
    FILTER(
        'CADUSD', 'CADUSD'[Data Type] <> "Plan"
    ),
    DATEADD(
        Calendar[Date], 0, MONTH
    )
)


PrevM = CALCULATE(
    SUM('CADUSD'[Balance]),
    FILTER(
        'CADUSD', 'CADUSD'[Data Type] <> "Plan"
    ),
    DATEADD(
        Calendar[Date], -1, MONTH
    )
)

 

 

 

dw2468_0-1710333615143.png

 

5 REPLIES 5
ray_aramburo
Super User
Super User

See my example attached on how to set up the measures, you can adjust as needed:

ray_aramburo_0-1710346428250.png

 

ray_aramburo_1-1710346454362.png

I used a similar structure of yours but changed my totals just to serve as an example.





Did I answer your question? Give your kudos and mark my post as a solution!

Proud to be a Super User!





ray_aramburo
Super User
Super User

You are filtering your data for February so in the filter context there's nothing to be shown for January but it appears since you included a Previous Month value as well. For Current Month you should remove the DateAdd portion of the measure, it is not needed.





Did I answer your question? Give your kudos and mark my post as a solution!

Proud to be a Super User!





if I remove the months filter, the CurrM column now has more than 1 values. I should expect CurrM to only show only the february number when i have DATEADD, 0, MONTH (see image attached)

 

dw2468_0-1710340865838.png

 

dw2468
Frequent Visitor

not sure why i did the same in my model and it wouldn't work. the result is the same as per my original screenshot.

Why would you need the Data Type column filter on the measure but then add it to the visual?





Did I answer your question? Give your kudos and mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.