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
Anonymous
Not applicable

YTD as of Last Completed Month

I've been using the following DAX to calculate YTD Gross Profit as of the last completed month (i.e. If I viewed the report on 11/10/19, I would see YTD Gross Profit for 1/1/19 to 10/31/19). 

 

 

GP-YTD =
CALCULATE (
    [Gross Profit],
    DATESYTD ( 'CALENDAR'[Date] ),
    MONTH ( 'CALENDAR'[Date] ) <> MONTH ( TODAY () )
)

 

 

It has worked flawlessly until January 2020 which is now returning BLANK. I am wanting it to return Gross Profit for 1/1/19 to 12/31/19. I do understand why it is returning BLANK, but I'm not sure how to tweak the formula to show YTD Gross Profit as of the last completed month once the date crosses into January of a new year.

 

FYI - My calendar table contains 5 years of data.

 

I also found this solution in the forums, but it returns a BLANK as well.

 

 

GP-YTD2 =
CALCULATE (
    [Gross Profit],
    FILTER (
        ALL ( 'CALENDAR' ),
        YEAR ( 'CALENDAR'[Date] ) = YEAR ( TODAY () )
            && MONTH ( 'CALENDAR'[Date] )
                <= MONTH ( TODAY () ) - 1
    )
)

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @az38 

 

I tried your method below (GP-YTD2) and was able to get it to return the correct number, but only if the result was loaded in a table with "Year" in the first column. When I tried putting the measure in a card, it also returned a blank. If I used a date slicer and filtered to 2020, I could get the value to show in a card. I know part of the problem is my misunderstanding of how certain measures aggregate to a grand total and other do not. 

 

Capture2.PNG

 

I was able to figure out a solution (not a pretty one), but I'm sure there's a better way to do this. I basically wrapped my previous measure in an IF statement and said if the current month is January, then return the previous year's Gross Profit. If the current month is not January, then use the previous DAX in my original post (GP-YTD) that works for all months except January.

GP-YTD3 =
IF (
    MONTH ( TODAY () = 1 ),
    CALCULATE (
        [Gross Profit],
        DATESYTD ( DATEADD ( 'CALENDAR'[Date], -1, MONTH ) )
    ),
    CALCULATE (
        [Gross Profit],
        DATESYTD ( 'CALENDAR'[Date] ),
        MONTH ( 'CALENDAR'[Date] ) <> MONTH ( TODAY () )
    )
)

 

View solution in original post

5 REPLIES 5
SheronKitchila
Regular Visitor

Hi @Anonymous Thank you this solution was helpful for me. I have another small query. So now with this YTD I need to get the previous YTD. Eg: Assume if we are in March 2021 so for YTD we should get Jan 2021 - Feb 2021 (Year to most recent full month) and then for previous YTD we should get 2020 Jan to 2020 Feb. Do you have any clue to cater this. Much appreciated if you could help

Anonymous
Not applicable

Greetings @SheronKitchila 

 

In order to get the prior year YTD from the example above (GP-YTD3), you would then create another measure using the SAMEPERIODLASTYEAR function.

 

Prior Year GP-YTD =
CALCULATE ( [GP-YTD3], SAMEPERIODLASTYEAR ( 'CALENDAR'[Date] ) )

Hello
could you help me with this?

i tried to do this formula for get the YTD LY but it doesn't work I copied the same formular for YTD and its works but for Last Year i couldn't get any values

az38
Community Champion
Community Champion

Hi @Anonymous 

You could create a calculated column

DateKey = 100*YEAR(Calendar[Date])+MONTH(Calendar[Date])

then modify your measure

GP-YTD2 =
CALCULATE (
    [Gross Profit],
    FILTER (
        ALL ( 'CALENDAR' ),
        YEAR ( 'CALENDAR'[Date] ) = YEAR ( EOMONTH(TODAY (),-1) )
            && [DateKey]< SELECTEDVALUE([DateKey])
    )
)

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

Hi @az38 

 

I tried your method below (GP-YTD2) and was able to get it to return the correct number, but only if the result was loaded in a table with "Year" in the first column. When I tried putting the measure in a card, it also returned a blank. If I used a date slicer and filtered to 2020, I could get the value to show in a card. I know part of the problem is my misunderstanding of how certain measures aggregate to a grand total and other do not. 

 

Capture2.PNG

 

I was able to figure out a solution (not a pretty one), but I'm sure there's a better way to do this. I basically wrapped my previous measure in an IF statement and said if the current month is January, then return the previous year's Gross Profit. If the current month is not January, then use the previous DAX in my original post (GP-YTD) that works for all months except January.

GP-YTD3 =
IF (
    MONTH ( TODAY () = 1 ),
    CALCULATE (
        [Gross Profit],
        DATESYTD ( DATEADD ( 'CALENDAR'[Date], -1, MONTH ) )
    ),
    CALCULATE (
        [Gross Profit],
        DATESYTD ( 'CALENDAR'[Date] ),
        MONTH ( 'CALENDAR'[Date] ) <> MONTH ( TODAY () )
    )
)

 

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.

Top Solution Authors