Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cumulative Previous Year over Year Calculation With custom start and end dates

Hi all,

 

I have a Date dimension and i have a basic fact table with a date column and a emissions number.

 

I have a line graph which shows the financial year and the cummlative sum of the selected year. The custom year is a datetime which is created in SQL so that the start of the year starts at 06/2021 to the end of 05/2021. 

 

Total Emissions Flights CY KPI =
var maxd = max(Periods[Date])
var calc=CALCULATE(SUM('Tandem Flights'[Emissions no RF (tCO2e)]),FILTER(ALLSELECTED(Periods), Periods[Date] <= maxd))
return cal

 

Is it possible to do a cummlative sum year over year for the previous year with the custom year start and year end?

 

Need Help
  • lbendlin's avatar
    lbendlin
    6 years ago

    If you already have a Calendar table you should maintain your custom year definition there (also known as Fiscal Year).

     

    I assume you want to compare the current year to date value to the value in the previous year for the same day range.

     

    Intriguingly you can nearly use SAMEPERIODLASTYEAR for that.  All you need to do is add a flag to your calendar table with a calculated column that says:

     

    IsPastPY = 
VAR LastFactDatePY = EDATE(MAX('Fact'[Day]),-12)
RETURN [date]<=LastFactDatePY

     

    Calculating Previous Year To Date then looks like this as a measure:

     

    PY := CALCULATE (
            [Value],
            SAMEPERIODLASTYEAR(Dates[date]),
            Dates[IsPastPY]=TRUE
        )

     

    As you may have noticed there is no need to specify the Fiscal Year boundaries - your Calendar table does that work for you.

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    If you already have a Calendar table you should maintain your custom year definition there (also known as Fiscal Year).

     

    I assume you want to compare the current year to date value to the value in the previous year for the same day range.

     

    Intriguingly you can nearly use SAMEPERIODLASTYEAR for that.  All you need to do is add a flag to your calendar table with a calculated column that says:

     

    IsPastPY = 
VAR LastFactDatePY = EDATE(MAX('Fact'[Day]),-12)
RETURN [date]<=LastFactDatePY

     

    Calculating Previous Year To Date then looks like this as a measure:

     

    PY := CALCULATE (
            [Value],
            SAMEPERIODLASTYEAR(Dates[date]),
            Dates[IsPastPY]=TRUE
        )

     

    As you may have noticed there is no need to specify the Fiscal Year boundaries - your Calendar table does that work for you.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Datesytd should help for year till date

     

    example

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"05/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"05/31"))

     

    Overall Cumulative 

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))
    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,year))))

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a