Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm struggling with dax formula that will give accumulated total ytd for 2022 and 2023 and exclude the current month. I appreciate help in advance.
It's a single table with columns Date,Country,Meetings,Events. What I'm trying to achieve is have TOTALYTD for both 2022 and 2023 populate in the cards when a year is NOT selected. If a year is selected it should populate the TOTALYTD for that year. The rolling total month over month ex: July to August should be the total for those two months shown in the card. 2023 total must not include the current month. I've included the pbix.
Date | Country | Meetings | Events |
1/1/2023 | USA | 2 | 4 |
1/2/2023 | Canada | 1 | 1 |
1/4/2023 | Canada | 3 | 2 |
2/1/2023 | England | 4 | 3 |
2/28/2023 | USA | 1 | 7 |
3/1/2023 | Canada | 6 | 6 |
7/1/2022 | England | 2 | 1 |
7/6/2022 | Germany | 1 | 1 |
8/1/2022 | France | 2 | 4 |
8/9/2022 | Belgium | 6 | 10 |
12/28/2022 | Italy | 4 | 3 |
12/28/2022 | Ireland | 8 | 16 |
12/31/2022 | Italy | 5 | 10 |
I have slicers to filter the data for Year, Month, Country. I have two cards for Events and Meetings.
I tried achieving my scenario with this formula but it does not work. See my testing scenario explaining what's occurring...
Scenario 1: As a user, I select Year 2022 the totals for Events and Meeting should populate in the respective cards. It is showing as blank.
Scenario 2: If as a user, I select 2022 and July it should populate July total. I select 2022 and August it should show the YTD total for 2022 from July to August. The same for 2023, excluding the current month.
Scenario 2: If as a user I did not select a year, it should show the total for 2022+2023 in the cards. It is only showing 2023 totals.
Since the above does not work I tried with TOTALYTD and nothing for EOMONTH. This gives the total but I would rather find how to exclude the current month. Also, if I don't select a year, the cards display the total for the current year.
@amitchandak, thank you for responding. None of those examples work as I'd like or just none at all. The first one doesn't show 2022 totals...shown as blank. The second does not show 2022 totals and for 2023 it gives just the month number. The third does not work at all.. selected year return as a blank. I appreciate you taking the time!
@jamaicawi , try like
YTD till Last month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),-1)
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
LYTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |