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
SingSong
Frequent Visitor

TotalYTD to Last Month

I have a costs statement source in the below format, I would like to return the Plan 2019 cost figure only to last month... so this month is currently May, I only want the sum from Jan to Apr returned

 

   Table 1 / Costs
-----------------------------
TimePeriod   | Date mm/dd/yyyy  | Value
-----------------------------
 A18         | 01/01/2018       | 12345
 A18         | 02/01/2018       | 12345
... A18 | 12/01/2018 | 12345 A19 | 01/01/2019 | 12345
A19 | 02/01/2019 | 12345
A19 | 03/01/2019 | 12345
A19 | 04/01/2019 | 12345
P19 | 01/01/2019 | 12345
P19 | 02/01/2019 | 12345
...
P19 | 12/01/2019 | 12345

I have a date table(Dates) which I have a relationship to the Date column above

 

P19 YTD = TOTALYTD(SUM(Costs[Value]),Dates[Date],FILTER(Costs,Costs[TimePeriod]="P19"))
 
I expected this measure to limit the sum for Jan to May's Plan figure then I was goign to figure out how to go back to last month but it SUM's P19 for Jan to Dec.
 
Any help much Appreciated
 
SingSong
1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @SingSong ,

 

To create a measure as below.

Measure = 
VAR pre =
    CALCULATE (
        MAX ( 'CALENDAR'[Date] ),
        FILTER ( 'CALENDAR', DATEDIFF ( 'CALENDAR'[Date], TODAY (), MONTH ) = 1 )
    )
RETURN
    CALCULATE (
        SUM ( Table1[value] ),
        FILTER (
            Table1,
            Table1[TimePeriod] = "P19"
                && YEAR ( 'Table1'[date] ) = YEAR ( pre )
                && 'Table1'[date] <= pre
        )
    )

 Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
littlemojopuppy
Community Champion
Community Champion

Hi!  I came across this looking for a solution for a similar problem.  I wanted to offer up the solution I came up with as an alternative or for anyone who might be looking in the future...

    CALCULATE(
        TOTALYTD (
            [Forecasted Contract Count],
            'Calendar'[Date]
        ),
        INTERSECT(
            DATESYTD('Calendar'[Date]),
            PREVIOUSMONTH('Calendar'[Date])
        )
    )

 

v-frfei-msft
Community Support
Community Support

Hi @SingSong ,

 

To create a measure as below.

Measure = 
VAR pre =
    CALCULATE (
        MAX ( 'CALENDAR'[Date] ),
        FILTER ( 'CALENDAR', DATEDIFF ( 'CALENDAR'[Date], TODAY (), MONTH ) = 1 )
    )
RETURN
    CALCULATE (
        SUM ( Table1[value] ),
        FILTER (
            Table1,
            Table1[TimePeriod] = "P19"
                && YEAR ( 'Table1'[date] ) = YEAR ( pre )
                && 'Table1'[date] <= pre
        )
    )

 Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Many thanks for your solution, worked perfectly

 

SingSong

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.