topic Re: Historical bands using a column in DAX Commands and Tips

Historical bands using a column

Gab_1983 — Mon, 31 Jul 2023 06:51:35 GMT

Hello,

I need to implement many classifications based on different measures (YTD, MTD). I'm using a column because I need the flexibility to use it also as a filter in a slicer.

The classification works well if I use it as a filter or in a matrix as a value. I have problems when I put it in a rows and I want to show in the matrix also the YTD amount (where the classification is based).

Schema:

Class Revenues =

VAR CLASS REVENUES= CALCULATE(SUM(FactAMT[AMT]),FILTER(Customer,Customer[Customer] = EARLIER(Customer[Customer])),DATESYTD(Period[Date]))

RETURN

IF(

CLASS > 6000000, "C>6mn",

IF(

CLASS > 3000000, "C 3-6mn",

IF(

CLASS > 0 , "C 0-3mn","NNA<0mn"

)

If I select a specific date I would like to show in the matrix the YTD amount and in the rows the classification of the specific period selected. The problem as you can see below is that the matrix shows me also the historical situation of the classification.

Through the link below you will find the example.

https://1drv.ms/u/s!AvVXApJDIqkLlz5Pwu-crnGBeFWv?e=2tNf6b

Thanks in advance for your support.

Re: Historical bands using a column

ERD — Mon, 31 Jul 2023 14:48:52 GMT

Hi Gab_1983 ,

You can try this measure instead:

REVENUES YTD = VAR t = ADDCOLUMNS ( SUMMARIZE ( Customer, Period[Date], Customer[Customer], Customer[Class Revenues] ), "REVENUE", CALCULATE ( SUM ( FactAMT[AMT] ), DATESYTD ( Period[Date] ), ALLEXCEPT ( Customer, Customer[Customer] ) ) ) RETURN SUMX ( t, [REVENUE] )

Re: Historical bands using a column

Gab_1983 — Mon, 31 Jul 2023 15:27:04 GMT

Hi ERD,

thanks for your suggestion. Effectively in this way it works. So according to you is not the "Class column" the problem but the measure...The problem is that in reality I have a big data model that already includes a lot of measures (more than 200), change the structure of each measure is not feasible.

I'm trying to find a way to implement multiples classification (based on MTD, YTD figures) considering the selected date.

I know that maybe use a column is not the best approach (I already tried to do the classification with a measure (measure + parametric table with the segmentation) and it works well but then I have the limitation that a measure can't be used in rows or in a slicer) .

Please let me know if you have any other suggestions.

Thanks in advance
G.