Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to Solution.
Hi,
You can ignore this message, i got one dax from @amitchandak, its working effectively. Thank you so much.
Previous Month =
var _min = eomonth(today(),-2)+1
var _max = eomonth(today(),-1) //today()
RETURN
CALCULATE(AVERAGE('Adoption Trend_Dup'[NewAdoption]), FILTER(ALL('Calendar Table'), 'Calendar Table'[Dates] >= _min && 'Calendar Table'[Dates] <=_max ) )
Hi,
You can ignore this message, i got one dax from @amitchandak, its working effectively. Thank you so much.
Previous Month =
var _min = eomonth(today(),-2)+1
var _max = eomonth(today(),-1) //today()
RETURN
CALCULATE(AVERAGE('Adoption Trend_Dup'[NewAdoption]), FILTER(ALL('Calendar Table'), 'Calendar Table'[Dates] >= _min && 'Calendar Table'[Dates] <=_max ) )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |