Help - Total Sales Last Year - Year does not begin on January 1st

[Damelau]

Hi PBI Community, 

Can anyone help me solve this one ?

I'm trying to calculate the Total Sales YTD for Last Year in order to compare with Total Sales YTD.

My issue is that we've a fiscal year starting on 1/10/YYYY and ending on 30/9/YY+1 

When I use sameperiodlastyear formula - it calculates YTD as from 1st January. But I cannot manage to combine it with a condition to solve my fiscal year issue.

Please can anyone help me ?

Thanks a lot

Lau

[SamMitchellTVG · Accepted solution]

Do you have a fiscal calendar with fiscal date and fiscal year? e.g.

Date	Fiscal_Year
30/09/2020	2019
01/10/2020	2020
02/10/2020	2020

If so, you could do the following.

Previous Year to Date =
VAR _PreviousYear =
    CALCULATE (
        MIN ( Calendar[Fiscal Year] ),
        Calendar[Date] = DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
    )
VAR _FirstDatePY =
    CALCULATE (
        FIRSTDATE ( Calendar[Date] ),
        Calendar[Fiscal Year] = _PreviousYear
    )
RETURN
    CALCULATE (
        [Total Sales],
        DATESBETWEEN (
            Calendar[Date],
            _FirstDatePY,
            DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
        )
    )

Get the previous year, then get the first date of the previous year. After that use DATESBETWEEN to get the data from the first date of the previous year, to the same day TODAY() minus one year.

[v-deddai1-msft · Accepted solution]

Hi @Damelau ,

Would you please try to create a calendar table:

Calendar = CALENDARAUTO()

Then create relationship between your fact table and calendar table, and you can use the following measure:

Result = 
var _year = SELECTEDVALUE('Calendar'[Date].[Year])
return

CALCULATE(
    SUM(Sales_Data[Sales]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date]>=DATE(_year-1,9,1)&&
        'Calendar'[Date]<=DATE(_year,Month(Today()),Day(Today()))
    )
)

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

Best Regards,

Dedmon Dai

[SamMitchellTVG · Accepted solution]

Do you have a fiscal calendar with fiscal date and fiscal year? e.g.

Date	Fiscal_Year
30/09/2020	2019
01/10/2020	2020
02/10/2020	2020

If so, you could do the following.

Previous Year to Date =
VAR _PreviousYear =
    CALCULATE (
        MIN ( Calendar[Fiscal Year] ),
        Calendar[Date] = DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
    )
VAR _FirstDatePY =
    CALCULATE (
        FIRSTDATE ( Calendar[Date] ),
        Calendar[Fiscal Year] = _PreviousYear
    )
RETURN
    CALCULATE (
        [Total Sales],
        DATESBETWEEN (
            Calendar[Date],
            _FirstDatePY,
            DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
        )
    )

Get the previous year, then get the first date of the previous year. After that use DATESBETWEEN to get the data from the first date of the previous year, to the same day TODAY() minus one year.

[amitchandak]

@Damelau , in datesytd and totalytd you can give end date to get ytd

Get Oct / Sep calendar

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

Formula

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

Power BI — YTD Questions — Time Intelligence 1–5
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.

[harshnathani]

Hi @Damelau ,

See if this helps.

Total Sales = Sum(Sales[Total Revenue])

Sales LY = CALCULATE([Total Sales], DATEADD(Dates[Date] ,-1,YEAR))

Sales FYTD = CALCULATE([Total Sales],DATESYTD(Dates[Date],"30/9"))

Sales LFYTD = CALCULATE([Sales LY],DATESYTD(Dates[Date],"30/9"))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)