topic Re: How Write Dax code to show trend Line on Year , Quarter , Month , Day Drill Downs in DAX Commands and Tips

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

Gopal_PV — Thu, 28 Mar 2024 16:04:47 GMT

Hi Friends,

Can you please help me in this.

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

Anonymous — Fri, 29 Mar 2024 09:48:00 GMT

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.

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

Gopal_PV — Fri, 29 Mar 2024 15:02:40 GMT

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 )