cancel
Showing results for
Did you mean:

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.

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?

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!

7 REPLIES 7
Community Champion

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

Displaying result in a Table visual

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.

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]))`

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

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?

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.

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.

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!

Announcements