cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ale
Advocate I
Advocate I

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
Ale
Advocate I
Advocate I

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.

PattemManohar
Community Champion
Community Champion

@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?

PattemManohar
Community Champion
Community Champion

@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

Ale
Advocate I
Advocate I

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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors