Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Thanks in advance!
Solved! Go to 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!
@Ale Please try this
Test148Prev = CALCULATE(SUM(Test148CurrPrevMth[CurrentVal]),PREVIOUSMONTH(_DimDate[Date]))
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]))
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?
@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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |