Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jamaicawi
Helper II
Helper II

How do I get TOTALYTD and EOMONTH for last year and the current year and exlude current month?

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.

 

DateCountryMeetingsEvents
1/1/2023USA24
1/2/2023Canada11
1/4/2023Canada32
2/1/2023England43
2/28/2023USA17
3/1/2023Canada66
7/1/2022England21
7/6/2022Germany11
8/1/2022France24
8/9/2022Belgium610
12/28/2022Italy43
12/28/2022Ireland816
12/31/2022Italy510

 

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...

_2022 and 2023 YTD Events excl current month =
TOTALYTD(SUM ('Table'[Events] ), 'Table'[Date].[Date],
    FILTER (
        'Table',
                'Table'[Date].[Date] >= DATE ( YEAR ( TODAY () ), 1, 1 )
            && 'Table'[Date] <= EOMONTH ( TODAY (), -1 )
    )
)
 

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.

 

Events YTD =
IF(
    ISFILTERED('Table'[Date].[Date]),
        ERROR("Check the date"),
    TOTALYTD(SUM('Table'[Events]), 'Table'[Date].[Date]
        )
)
 
I've tried attaching the PBIX file to this but not sure that I see how to! I appreciate your help and advice in advance.
2 REPLIES 2
jamaicawi
Helper II
Helper II

@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!

amitchandak
Super User
Super User

@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))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.