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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ale
Resolver II
Resolver II

Calculate Previousmonth not working with Slicer

 

Hi there,

 

my question might be simple but I've been stuck here for a while.

 

I'm trying to get the values for current month and previous month. I created 2 Measures to do that (CurrentVal and PreviousVal).

My DAXs are:

CurrentVal = SUM(Database[KPI_Value])

PreviousVal = CALCULATE([CurrentVal]; PREVIOUSMONTH(DIM_Date[Date]))

 

 

In the image below, when displayed as a table, you can see it's working fine.

 

Image01.PNG

 

However, I don't want a table like that, but instead I want a slicer where the user can select the month and he will be able to see the current and previous month values, just like the image below. The problem, as you can see, is that when I remove the Date from the table and include the slicer, the PreviousVal returns always blank (in this example, it should return 0,44). What am I doing wrong here?

 

Image02.PNG

 

 

 

Thanks in advance!

 

 

1 ACCEPTED SOLUTION

Appreciate the help of our colleague here. I found what was missing.

I haven't set my date dimensions as a Date Table.

To do so, just go to Table view -> Modelling tab -> Mark as Date Table.

Now it works perfectly. Thanks again!

View solution in original post

7 REPLIES 7
PattemManohar
Community Champion
Community Champion

@Ale Please try this

 

Test148Prev = CALCULATE(SUM(Test148CurrPrevMth[CurrentVal]),PREVIOUSMONTH(_DimDate[Date]))

image.pngDisplaying result in a Table visualDisplaying result in a Table visual





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




My CurrentVal is already a SUM aggregation of the KPI_Value.

 

If I try to use SUM again, Power BI does not accept it. I tried to change the formula to do the SUM directly, as following

 

PreviousVal = CALCULATE(SUM(Database[KPI_Value]); PREVIOUSMONTH(DIM_Date[Date]))

, instead of using CurrentVal, but the result is still the same.

@Ale I tried having another measure for current month and using that in PrevMonth measure as well

 

Test148Curr = SUM(Test148CurrPrevMth[CurrentVal])
Test148Prev = CALCULATE(_KeyMeasures[Test148Curr],PREVIOUSMONTH(_DimDate[Date]))

image.png

It's working fine... Hope you are using the Date Slicer from Date dimension table.





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Ok, I think you just pointed out what was wrong!!

 

I changed the slicer from Date (from date dimension) to YYYY-mm, because the user should selected the month, instead of a full date. That's why this is not working.

 

In this case, how could I solve that? Is there a way the user only select the Year/month (Year-MonthNumber field) in the slicer and I still get the result?

@Ale In that case you need to have a YYYY-MM format value in your date dimension and use that in your slicer.

 

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




I actually do, but the problem persists. Weird. This YearMonthnumber is from the Date dimension, same place where I was pulling the Date. Image below.

Image03.PNG

Appreciate the help of our colleague here. I found what was missing.

I haven't set my date dimensions as a Date Table.

To do so, just go to Table view -> Modelling tab -> Mark as Date Table.

Now it works perfectly. Thanks again!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.