Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
I'm using the measurement below for the previous month, but when the card's visual is not selected it is displayed (blank). I want to display a constant value for the previous month and change the accordion month selection in the slicer.
please guide
Recuento de mes anterior = VAR __currentDate = EOMONTH ( MAX ( Sheet1[date] ), -1 ) RETURN CALCULATE ( COUNTROWS ( Sheet1 ), DATESBETWEEN ( Sheet1[date], EOMONTH ( __currentDate, -1 ) + 1, __currentDate )
Solved! Go to Solution.
Hi @Syndicate_Admin ,
Causes of the problem:
When calculating the data for the first month, the original table does not have the data for the month before the first month
You can use the following steps:
Step 1,
Create a date table, just like if the before table date from 2020.2.1 to2021.3.1 ,then date table from 2020.1.1 to 2021.3.1
Table2 = CALENDAR(EOMONTH (MIN('Sheet1'[Date]),-2)+1,MAX(('Sheet1'[Date])))
Step 2,
Use the following measure:
Recuento de mes anterior = CALCULATE (
COUNTROWS (Table2),
DATESBETWEEN (Table2[date],
EOMONTH (max(Table2[Date]), -2) + 1,
EOMONTH (max(Table2[Date]), -1)
))
Wish it is helpful for you!
Click here to download pbix if you need.
Best Regard
Lucien Wang
Hi @Syndicate_Admin ,
Causes of the problem:
When calculating the data for the first month, the original table does not have the data for the month before the first month
You can use the following steps:
Step 1,
Create a date table, just like if the before table date from 2020.2.1 to2021.3.1 ,then date table from 2020.1.1 to 2021.3.1
Table2 = CALENDAR(EOMONTH (MIN('Sheet1'[Date]),-2)+1,MAX(('Sheet1'[Date])))
Step 2,
Use the following measure:
Recuento de mes anterior = CALCULATE (
COUNTROWS (Table2),
DATESBETWEEN (Table2[date],
EOMONTH (max(Table2[Date]), -2) + 1,
EOMONTH (max(Table2[Date]), -1)
))
Wish it is helpful for you!
Click here to download pbix if you need.
Best Regard
Lucien Wang
You can create a below measure to calculate previous month value
= CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSMONTH('DateTime'[DateKey]))
Proud to be a Super User!