Forum Discussion

rsyashwini's avatar
rsyashwini
Helper I
6 years ago
Solved

DAX Help - All filter

I have filter Month and below measures

[Measure] - Mesaure1

[x]= June ( example)

[SelectedMonth]= November (Monthselected in filter )

NewMeasure - To be calculated

 

When I select Month filter ( November), NewMeasure calculation should look back X month till selected month (November)

 

NewMeasure= CALCULATE(Measure1,FILTER(ALL(Table1),Table1[report_month]>X && Table1[report_month]<=[SelectedMonth] ))
 
I was hoping, from above DAX, 
ALL(Table1) - I will remove Month filter  and then apply new filter in DAX that will calculate between June and November
 
Newmeasure shows value for all months( since I removed filter using ALL(Table1)
It does not look into filter expression Table1[report_month]>X && Table1[report_month]<=[SelectedMonth]
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    Create a Calendar Table and write calculated column formulas to extract Year, Month number and Month Name.  Sort the Month name by the month number.  Create a relationship from the Date column of your source data table to the Date column of your Calendar Table.  To your slicer, drag Year and Month Name from the Calendar Table and select November > 2020.

    Write this measure

    =calculate([Measure1],datesbetween(calendar[date],date(2020,6,1),max(calendar[date])))

    Hope this helps.

4 Replies

  • hohlick's avatar
    hohlick
    Continued Contributor
    6 years ago

    Hi rsyashwini 

    It looks like you use the month from the same table in the slicer, aren't you?

    The reason why your measure doesn't work is that you put the measure [SelectedMonth] in FILTER, so it calculated in the row context made by FILTER. In other words, it calculated for each row of the Table1, looking for the report_month column in it, and then Table1[report_month]<=[SelectedMonth] is always TRUE.

    Do not sure if you need ALL(Table1) in the filter. May be ALL(Table1[repot_month]) is enough?

    You need to evaluate [SelectedMonth] in the variable before placing it in the FILTER:

    =
    VAR _SelectedMonth = [SelectedMonth]
    RETURN
        CALCULATE(
            [Measure1],
            Table1[repot_month] >= X,
            Table1[repot_month] <= _SelectedMonth
        )

    or, if you really need ALL(Table)

    =
    VAR _SelectedMonth = [SelectedMonth]
    RETURN
        CALCULATE (
            [Measure1],
            FILTER (
                ALL ( Table1 ),
                Table1[report_month] >= X
                    && Table[report_month] <= _SelectedMonth
            )
        )

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Create a Calendar Table and write calculated column formulas to extract Year, Month number and Month Name.  Sort the Month name by the month number.  Create a relationship from the Date column of your source data table to the Date column of your Calendar Table.  To your slicer, drag Year and Month Name from the Calendar Table and select November > 2020.

    Write this measure

    =calculate([Measure1],datesbetween(calendar[date],date(2020,6,1),max(calendar[date])))

    Hope this helps.