YTD and FY LY figures

[lewand03]

Hi All,

In a one table I have to present YTD, LY YTD, and FY Last Year Figures.

For YTD and LY YTD figure it's pretty simple, as I am using TTOTAL YTD Formula:

YTD = TOTALYTD(sum(Facts[Value]),'Calendar'[Date])

YTD LY = CALCULATE([YTD],SAMEPERIODLASTYEAR('Calendar'[Date]))

How to create measure to display FY LY next to that?

For example for data like that:

Year	Period	Value
2015	1	10
2015	2	10
2015	3	10
2015	..	80
2015	12	10
2016	1	20
2016	2	20
2016	3	20
2016	..	160
2016	12	20

I want to se result like that:

Year	Period	CY YTD	LY YTD	FY LY
2016	3	60	30	120

Thanks,

DL

[lewand03 · Accepted solution]

The issue I had was with sorting field.

I had Month Name as Slicer, which was sorted but Month Number.

Wnen I was using ALL(Month Name) It was not working, but by adding ALL(Month Name, Month Number) to my formula, then the slicer was finally ignored something like that:

FY LY = CALCULATE(SUM(Facts[Value]),SAMEPERIODLASTYEAR('Calendar'[Date]),ALL(Period, Period sort))

Thank you everyone for replying.

Regards,

D.

[mattbrice]

TOTALYTD has a parameter field to specify a FY end date.  So you can do this:

FY LYTD = TOTALYTD ( SUM(Facts[Value] ), SAMEPERIODLASTYEAR( Calendar[Date] ), "6/30"  ) 

[lewand03]

Thanks Mattbrice,

Your solution is good for MAT (moving annual total) bo it is not showing FY LY figures.

I have solved my issue by using parametrs for year and period and based on that I am calculating from-to date and by using FILTER I am able to get desired result, however I am looking for more dynamic solution...

Regards,
dariusz

[v-shex-msft]

Hi @lewand03,

If date function not suitable for your requirement, you can try to manually calculate the date range, then use them as part of filter.

Sample:

LYTD =
VAR current_date =
    MAX ( table[date] )
RETURN
    CALCULATE (
        SUM ( table[amount] ),
        FILTER (
            ALLSELECTED ( table ),
            [Date]
                >= DATE ( YEAR ( current_date ) - 1, 1, 1 )
                && [Date]
                    <= DATE ( YEAR ( current_date ) - 1, MONTH ( current_date ), DAY ( current_date ) )
        )
    )

BTW, I don't think you can convert current date to accurate FY date, you only need to care about to Fiscal month.

Regards,

Xiaoxin Sheng

[lewand03]

Thanks Xiaoxin,

YTD with "sameperiodlastyear" is working perfectly fine for me. I'd like to present FY LY dynamically (so independent on selected period). Your solution works in similar way to selection as a parameter.

BTW

I put it as a new idea to have TOTALFY formula as well, so if you also miss you please vote: link

Thanks,

D.