How Write Dax code to show trend Line on Year , Quarter , Month , Day Drill Downs

[Gopal_PV]

Hi Friends,

Can you please help me in this. 

[Anonymous]

Hi @Gopal_PV ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the measure to calculate month.

MonthLine = 
VAR day_ = CALCULATE(SUM('Table test'[Product sales]), FILTER(ALLEXCEPT('Table test', 'Table test'[Month]), MIN('Table test'[Month])))
VAR al_sum = SUM('Table test'[Product sales])
VAR result = DIVIDE(al_sum, day_)
RETURN
result

3.Create the measure to calculate Year.

YearLine = 
VAR day_ = CALCULATE(SUM('Table test'[Product sales]), FILTER(ALLEXCEPT('Table test', 'Table test'[Year]), MIN('Table test'[Year])))
VAR al_sum = SUM('Table test'[Product sales])
VAR result = DIVIDE(al_sum, day_)
RETURN
//FORMAT(result, "0.0%")
result

4.Drag the measure into the line and column chart visual. Select the percentage.

5.The result is shown below.

Best Regards,

Wisdom Wu

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

[Gopal_PV]

Hi @v-jiewu-msft 

Thank you for your reply. I want to see Year/ Quarter/Month/ Day value on Visual. where i will drill down to . Based drill it should show the Trendline percentage.

Trend % Line =
VAR CVal = Master[ProductCount]
VAR PVal =
SWITCH (
TRUE (),
ISINSCOPE ( Master[dt]),
CALCULATE (
Master[ProductCount],
DATEADD ( Master[dt], -1, DAY)
),

ISINSCOPE ( Master[fscl_Mnth_nbr]),
CALCULATE (
Master[ProductCount],
PARALLELPERIOD ( Master[dt], -1, MONTH ))
,
ISINSCOPE ( Master[fscl_qtr_lbl]),
CALCULATE (
Master[ProductCount],
PARALLELPERIOD ( Master[dt], -1, QUARTER )
),
ISINSCOPE ( Master[fscl_yr_nbr]),
CALCULATE (
Master[ProductCount],
PARALLELPERIOD ( Master[dt], -1, YEAR )
)
)
RETURN
DIVIDE ( CVal - PVal, PVal )