Previous Year Measure YTD if current year

[meddojeddo]

I have a data extract that contains Sales sliced by Branch and by Store and Year and at lower grains. I want to create a table visual in PBI which displays rows broken down by Year and one column displays sum of Sales and I want another column displaying previous year Sales for the year in that row. And for the current year, the previous year Sales would be YTD. 

So it should look something like this:

What would be the best way to go about this?

[amitchandak]

@meddojeddo , In case you do have date try like . Create a separate Year Table and join with your year in the table

example

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Year '),'Year '[Year]=max('Year '[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Year '),'Year '[Year]=max('Year '[Year])-1))

if you ave date

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

refer Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[meddojeddo]

Thanks. Just to clarify though, the current year column is fine as is, that data is YTD for current year. What I need is for the Previous Year column to display YTD for this year's "Last Year" value but the full year value for all previous years' "Last Year" values, if that makes sense. I need something like an IF condition, but I don't know how to go about that, since I cannot use that with a measure. So is there another way?

[amaleranda]

@meddojeddo ,

someting like below is your requirment? I have copied the related measures. use the column name you want to use in your measure.

Current Total =

VAR Result =

SUM ( Sheet1[Total] )

RETURN

Result

============

Total YTD =

VAR Result =

TOTALYTD (

[Current Total],

'Date'[Date]

)

RETURN

Result

======

Total YTD LY =

VAR Result =

CALCULATE([Total YTD],

SAMEPERIODLASTYEAR('Date'[Date]

)

)

RETURN

Result

[meddojeddo]

@amaleranda What I am actually looking for is just a column for sales and another column for LY sales. But the LY column displays the value for the previous year of the that row(e.g. the LY value for the 2020 row will display sales for 2019). Also, the LY value for the current year(2020) should display the YTD sales for 2019 as opposed to the whole year's sales. But the LY values for the rest of the rows(past years) should display the full year's sales for the previous year(e.g. the LY value in the 2019 row will display the full year's sales for 2018, not YTD). Is this possible?

[Icey]

Hi @meddojeddo ,

Please check if this is what you want:

Measure = 
IF (
    MAX ( Dates[Year] ) = YEAR ( TODAY () ),
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            'Table',
            'Table'[Date] >= DATE ( 2020, 1, 1 )
                && 'Table'[Date] <= TODAY ()
        )
    ),
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        SAMEPERIODLASTYEAR ( Dates[Date] )
    )
)

Measure 2 = 
SUMX ( VALUES ( Dates[Year] ), [Measure] )

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[meddojeddo]

@Icey I actually do not have a day value, I only have period, which is 202001,202002, etc. and year. So sameperiodlastyear won't work. I replaced it with a calculate(sum(sales), filter(period-100)) but when I use this it filters the measure so that only the 2020 and 2019 rows are populated in the chart. 

[Icey · Accepted solution]

Hi @meddojeddo ,

Sorry to reply late. Please check if the attachment is helpful.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.