Forum Discussion

Gab_1983's avatar
Gab_1983
New Member
3 years ago

Historical bands using a column

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.

G.

DAX

2 Replies

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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] )

    • Gab_1983's avatar
      Gab_1983
      New Member
      3 years ago

      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.