Hello,
I need to calculate the previous month data(April) for this year to be shown in CARD visual. Data has the value since last year May (2023) to till date. When I try few measures it throws 0% and blank values. And when I use other dax function, its providing Average from Jan to May 2024 value. Request your help on this.
Note: I have the calendar table which I created for fiscal year, Quarter, Month Name, Month separately. And whenever the month changes(from May to June), it has to show the next previous month(May) month value.
Used Measures:
Check=
var Month_ = Month(TODAY())
VAR last_rows = CALCULATE(COUNTROWS('Adoption Trend_Dup'), FILTER('Adoption Trend_Dup', 'Adoption Trend_Dup'[Month] = (Month_ - 1)))
VAR last_adop = CALCULATE(AVERAGE('Adoption Trend_Dup'[NewAdoption]), FILTER('Adoption Trend_Dup', 'Adoption Trend_Dup'[Month] = (Month_ - 1)))
var result = DIVIDE(last_adop, last_rows)
RETURN
IF(Month_ = 1, BLANK(), FORMAT(result, "0%"))
Previous Month =
CALCULATE('Adoption Trend_Dup'[AdoptionTrend%], PREVIOUSMONTH('Adoption Trend_Dup'[TxnDt])
)
Previous Month =
CALCULATE(AVERAGE('Adoption Trend_Dup'[NewAdoption]),DATESBETWEEN('Calendar Table'[Dates], EOMONTH(TODAY(),-14)+1, EOMONTH(TODAY(),-13)))
Previous Month =
CALCULATE(AVERAGE('Adoption Trend_Dup'[NewAdoption]),
YEAR('Calendar Table'[Dates]) = YEAR(TODAY()),
Month('Calendar Table'[Dates] = MONTH(TODAY()-1)))
Data Examples:
| Store Id | Store Name | NewAdoption | TxnDt |
| 1 | AA | 0% | Sunday, May 5, 2024 |
| 2 | BB | 80% | Sunday, May 5, 2024 |
| 3 | CC | 82% | Sunday, May 5, 2024 |
| 4 | DD | 56% | Sunday, May 5, 2024 |
| 10 | BBB | 0% | Sunday, May 5, 2024 |
| 11 | AAA | 78% | Sunday, May 5, 2024 |
| 12 | AAAA | 28% | Sunday, May 5, 2024 |
| 13 | AAAAA | 16% | Sunday, May 5, 2024 |
| 14 | BBBBB | 2% | Sunday, May 5, 2024 |
| 1 | AA | 0% | Monday, April 1, 2024 |
| 2 | BB | 80% | Monday, April 1, 2024 |
| 3 | CC | 82% | Monday, April 1, 2024 |
| 4 | DD | 56% | Monday, April 1, 2024 |
| 5 | EE | 92% | Monday, April 1, 2024 |
| 6 | FF | 0% | Monday, April 8, 2024 |
| 7 | GG | 6% | Monday, April 8, 2024 |
| 8 | HHH | 0% | Monday, April 8, 2024 |
| 12 | AAAA | 28% | Monday, April 15, 2024 |
| 13 | AAAAA | 16% | Monday, April 15, 2024 |
| 14 | BBBBB | 14% | Monday, April 15, 2024 |
| 1 | AA | 0% | Monday, January 1, 2024 |
| 2 | BB | 80% | Monday, January 8, 2024 |
| 3 | CC | 82% | Wednesday, January 10, 2024 |
| 4 | DD | 56% | Sunday, January 21, 2024 |
| 5 | EE | 92% | Thursday, February 1, 2024 |
| 6 | FF | 0% | Thursday, February 8, 2024 |
| 7 | GG | 6% | Sunday, February 11, 2024 |
| 8 | HHH | 51% | Tuesday, February 13, 2024 |
| 9 | AAA | 39% | Friday, March 1, 2024 |
| 10 | BBB | 0% | Saturday, March 2, 2024 |
| 11 | AAA | 78% | Sunday, March 3, 2024 |
| 12 | AAAA | 28% | Monday, March 4, 2024 |
| 13 | AAAAA | 16% | Tuesday, March 5, 2024 |
| 14 | BBBBB | 14% | Wednesday, March 6, 2024 |
| 1 | AA | 0% | Saturday, April 1, 2023 |
| 2 | BB | 23% | Sunday, April 2, 2023 |
| 3 | CC | 43% | Monday, April 3, 2023 |
| 1 | AA | 66% | Monday, May 1, 2023 |
| 2 | BB | 77% | Tuesday, May 2, 2023 |
| 3 | CC | 0% | Wednesday, May 3, 2023 |
