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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors