Fiscal Year - YTD sales for previous years

[Anonymous]

Hi All , @Greg_Deckler @amitchandak 

Fiscal year in my dataset starts from 01/Sep - 31/Aug .

Sample data sheet has sales data until  25/Mar/2020.

I want to add my sales from 01/sep/19 till 25/Mar/20 ; 01/sep/18 till 25/Mar/19 and so on for previous years. 

Download sample sales data:

https://we.tl/t-tmMSnmlZGb 

Expected output based on data set shared:

I have tried solution from the following post, but it did not worked out in this case

https://community.powerbi.com/t5/Desktop/Last-Year-and-Year-before-with-30-June-Fiscal-Year/m-p/9132...

https://community.powerbi.com/t5/Desktop/Previous-Fiscal-YTD-DAX-Not-working/m-p/906945#M434676

All dates are is date/time format in SQL Database

Language selection of powerbi and system is english(us)

[Date] table is marked as Date Table

Relationship is created between Sales table and date table 1-0-1 

Date table is created as follows:

Date =

VAR MinYear = YEAR(MIN(Sales_Data[Sales_Date]))

VAR MaxYear = YEAR(MAX(Sales_Data[Sales_Date]))

RETURN

ADDCOLUMNS (

FILTER (

CALENDARAUTO( ),

AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )

),

"FY YEAR", IF(MONTH([Date]) >=9 , (YEAR([Date]) + 1), YEAR([Date])),

"Calendar Year", "CY " & YEAR ( [Date] ),

"Month Name", FORMAT ( [Date], "mmmm" ),

"Month Number", MONTH ( [Date] ),

"Weekday", FORMAT ( [Date], "dddd" ),

"Weekday number", WEEKDAY( [Date] ),

"Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1

)

Kindly guide, where is it going wrong

Thank you very much 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on my research, you may create a calculated table with the following formula.

Calendar = CALENDARAUTO()

There is a one-to-one relationship between two tables. Then you can create a measure as below.

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,3,25)
    )
)

Result:

Best Regards

Allan

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

[Anonymous]

@v-alq-msft 

Hi Allan,

I modified a bit to get the same date last year and also included the leap year condition. It works well as expected.
But, I was wondering, if there is any function to get the same output

Result =
var _FYyear = SELECTEDVALUE('Date'[FY YEAR])

return

CALCULATE(
SUM(Sales_Data[Sales]),
FILTER(
         ALL('Date'),
         'Date'[Date] >= DATE(_FYyear - 1 , 9, 1) &&
         'Date'[Date] <= IF(TODAY() <= DATE(_FYyear - 1, 12, 31),
                                    DATE( _FYyear - 1 ,MONTH(TODAY()),DAY(TODAY())),
                                     IF(MOD(_FYyear,4) = 0 && MONTH(TODAY()) = 2 && DAY(TODAY()) = 29, BLANK(),
                                          DATE( _FYyear ,MONTH(TODAY()),DAY(TODAY()))))
) )

[Anonymous]

@allan Thank you for your reponse

In actual database, it will not be 1-0-1, this was a sample data and  25 date will be a variable, becasue the report get autorefresh every day. Therefore for all the previous year the date will be incremental.

Can you please  suggest.