Moving Difference - 1st selected month should be 0

JackWren — Wed, 06 Oct 2021 12:33:20 GMT

Hi everyone,

I have recently created different measures to see the evolution of my data month by month and to have the line value that shows the difference in evolution between selected months.

1/ Evolution

Evolution =

AVERAGEX (

VALUES ( 'DWH Calender'[DATES] ),

VAR EndofMonth1 = max('DWH Calender'[MONTH_END_DT])

VAR BeforeStartdate =

FILTER (

ALL ( 'SplitPlan Table'[ASSIGN_START_DT]),

'SplitPlan Table'[ASSIGN_START_DT] <= EndofMonth1

)

VAR AfterEnddate =

FILTER (

ALL('SplitPlan Table'[ASSIGN_END_DT] ),

'SplitPlan Table'[ASSIGN_END_DT]>= EndofMonth1

)

RETURN

CALCULATE (

DISTINCTCOUNT('SplitPlan Table'[NA]),

BeforeStartdate,

AfterEnddate,

ALL( 'DWH Calender' )

)

The measures for the line chart

A/ Evolution Previous Period

EvolutionPreviousPeriod =

Var Interval = calculate(

datediff(

max('DWH Calender'[DATES]),

min('DWH Calender'[DATES]),MONTH

ALLSELECTED('DWH Calender'[MONTHS])

)

RETURN

CALCULATE(

[Evolution],

PARALLELPERIOD('DWH Calender'[DATES],-2,MONTH)

)

B/ Difference between months to have the line chart

calculate([Evolution]-[EvolutionPreviousPeriod])

I have two problem

1 - I wish to make the interval for EvolutionPreviousPeriod dynamic

But this measure returns 0 in the visual even though when I check the variable in itself the result is correct.

EvolutionPreviousPeriod =

Var Interval = calculate(

datediff(

max('DWH Calender'[DATES]),

min('DWH Calender'[DATES]),MONTH

ALLSELECTED('DWH Calender'[DATES])

)

RETURN

CALCULATE(

[Evolution],

PARALLELPERIOD('DWH Calender'[DATES],Interval,MONTH)

)

2- My colleague have asked me to display 0 for the first month instead of the actual difference with the previous month.

Like this

Has anyone ever encountered something like that?

Thank you

Jack

Re: Moving Difference - 1st selected month should be 0

amitchandak — Thu, 07 Oct 2021 03:40:20 GMT

JackWren , Try measure like

Measure =
Var _min = minx(allselected('DWH Calender'),'DWH Calender'[DATES])
var _month = minx(filter(allselected('DWH Calender') , 'DWH Calender'[DATES] =_min),'DWH Calender'[Year Month])
return
if(max('DWH Calender'[Year Month]) =_month, blank(), [EvolutionPreviousPeriod])

Re: Moving Difference - 1st selected month should be 0

JackWren — Thu, 07 Oct 2021 11:25:13 GMT

amitchandak Thank you so much!

It works perfectly well 🙂

topic Re: Moving Difference - 1st selected month should be 0 in DAX Commands and Tips

Moving Difference - 1st selected month should be 0

Re: Moving Difference - 1st selected month should be 0

Re: Moving Difference - 1st selected month should be 0