topic Re: Need help on conditional format previous week sales in DAX Commands and Tips

Need help on conditional format previous week sales

Anonymous — Fri, 13 Nov 2020 13:35:32 GMT

Hi, I want this table to show icons (arrows up, down or equal) if the previous week sales is higher, lower or equal to the sales of the week of that column. Eg: Sales of "boller" in week 43 is higher than in week 42, thus must the arrow point upwards. The other way around for sales of Brød in week 43, that should be downward.

This is my DAX code:

Icon =

VAR _val =

CALCULATE (

SUM ( Datagrunnlag[Antall solgt]))

CALCULATE (

SUM (Datagrunnlag[Antall solgt]),Datagrunnlag,Datagrunnlag[Week number]-1)

RETURN

SWITCH ( TRUE (),

_val < 0, -1,

_val = 0, 0,

_val > 0, 1 )

then i use conditional formatting based on rule "Icon"

Re: Need help on conditional format previous week sales

kumar27 — Fri, 13 Nov 2020 14:04:37 GMT

Let us make it simple .

Use 2 variables.

One for current week and for that use max .

var 2 will have sum with MAX(column name)-1

Now use the calculation in switch statement.

Thanks

Re: Need help on conditional format previous week sales

AlB — Fri, 13 Nov 2020 16:37:20 GMT

Hi Anonymous

Are you asking how to build the measure or how to display the arrows? Or both?

For the measure try this:

Icon = VAR currentWeekVal_ = CALCULATE ( SUM ( Datagrunnlag[Antall solgt] ) ) VAR previousWeekVal_ = CALCULATE ( SUM ( Datagrunnlag[Antall solgt] ), FILTER ( ALL ( Datagrunnlag[Week number] ), Datagrunnlag[Week number] = MAX ( Datagrunnlag[Week number] ) - 1 ) ) VAR _val = currentWeekVal_ - previousWeekVal_ RETURN SWITCH ( TRUE (), _val < 0, -1, _val = 0, 0, _val > 0, 1 )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers