How to calculate Sales for select period (Dynamic)

[Anonymous]

Hi Team !

I'm Struggling with the select period date values for LAST YEAR sales ,

For select period Current Year Sale for DAX is as follows please suggest a DAX for Last YEAR considering followin Current Year DAX.

CY Select Period =

VAR TodayDate = TODAY()

VAR CurrentYear = YEAR ( TodayDate )

VAR FilteredYear = YEAR ( MAX ( Dates[Date] ) )

VAR YearShift = FilteredYear - CurrentYear

VAR YTDTotal =

IF(

ISFILTERED(Dates),'Sale Calculation'[Total Sales],

CALCULATE(

'Sale Calculation'[Total Sales],

DATEADD (

DATESYTD ( TREATAS( {TODAY()} ,Dates[Date] ) ),

YearShift,

YEAR

)

)

)

RETURN

COALESCE ( YTDTotal, 0 )

When i select any random date e.g 11/11/2021 to 05/02/2022 then the select date Previous year  i.e11/11/2020 to 05/02/2022 values should appear but 

but it calculate 2021 year total sales.
Pl

[Anonymous · Accepted solution]

Hi @Anonymous ,

If you want to calcualte the total sales for previous period of selected period, you could create a flag measure:

Flag = 
var _min=DATE(YEAR(MIN('Dates'[Date]))-1,MONTH(MIN('Dates'[Date])),DAY(MIN('Dates'[Date])))
var _max=DATE(YEAR(MAX('Dates'[Date]))-1,MONTH(MAX('Dates'[Date])),DAY(MAX('Dates'[Date])))
return IF( MAX('Sale Calculation'[Date]) >=_min && MAX('Sale Calculation'[Date])<=_max,1,0)

And apply it to visual-filter pane, set as "is 1":

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

[danielboi]

How about an all selected on your slicer values. Extract min and max date out of that filter context and then reduce both dates by one year and use this in a calculate function?

[Anonymous]

Hi Daniel,

Actually Not getting can you please form a DAX as per your knowlegde here  and will try it on my measures .
Even i'm New to Power BI.
Hope you getting me.

[danielboi]

Hi @Anonymous ,

cannot comment on your formula, but did you try use 

DATESYTD & SAMEPERIODLASTYEAR

CALCULATE('Sale Calculation'[Total Sales],          <-Measure or column? If column might need to put it into sum()

DATESYTD(

SAMEPERIODLASTYEAR(Dates[Date])

)

)

[Anonymous]

YES but using this DAX i'm just fecthing last year whole sales total