Forum Discussion

gunjan80's avatar
gunjan80
Microsoft Employee
6 years ago
Solved

Filter using last date not working

I have a Sales table. I am trying to get the sale amount as per the last sale quarter.   I've tried, using 'LastDate' function. It works independantly  but it does not work with the filter combinatio...
Need Help
  • gunjan80's avatar
    gunjan80
    6 years ago

    I fixed this. Here is the DAX.

     

    Last Sale Amount2 = CALCULATE(sum(Table1[Sales]), All('Date'[Quarter]), CALCULATETABLE(LASTDATE(Table1[Date]),ALLEXCEPT(Table1, 'Table1'[Customer Name]) ))
     
     

     
     
     

     

     
     
     
     
     
     
     
     
     
     
     
     

     

     

     

     

     

     

     

     

     

     

     
     

     

     

gunjan80's avatar
gunjan80
Microsoft Employee
6 years ago

I need this data for forecasting purpose. Forecast calculations will be based on  'Current Sale Amt". Hence for all future quarters I need to use the data of the present quarter (present quarter could be Q3 or Q2 or Q1 or Q4). Below is the type of output I need in the form of a matrix. It works perfectly well with the below DAX, but the problem with it is after every quarter I will have to manually change the quarter from Q2 to Q3 / Q3 to Q4 etc. I tried using last date and Max functions but they dont work in this case. 

 

Current Sale Amt = CALCULATE(sum(Table1[Sales]), FILTER(ALLEXCEPT(Table1, 'Table1'[Customer Name]), 'Table1'[Quarter] = "Q2" ))

 

C1Q1Q2Q3Q4
Actual Sale100150  
Current Sale Amt150150150150
C2Q1Q2Q3Q4
Actual Sale200250  
Current Sale Amt250250250250
C3Q1Q2Q3Q4
Actual Sale300350  
Current Sale Amt350350350350
     

 

Thanks.

 

v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
6 years ago

Hi gunjan80 ,

 

We can create a date table and a measure to meet your requirement.

 

1. Create a whole year date table, add a quarter column and there is no relationship.

 

Date = CALENDAR("2020/1/1","2020/12/31")
Quarter = "Q" &""& QUARTER('Date'[Date])

 

 

2. Then create a measure and use the Date[Quarter] to create a matrix table.

 

Current Sale Amt = 
VAR _x =
    CALCULATE (
        SUM ( Table1[Sales] ),
        FILTER (
            ALLEXCEPT ( Table1, 'Table1'[Customer Name] ),
            'Table1'[Quarter] = MAX ( 'Date'[Quarter] )
        )
    )
VAR _y =
    CALCULATE (
        SUM ( Table1[Sales] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Customer Name] ),
            Table1[Quarter] = MAX ( Table1[Quarter] )
        )
    )
RETURN
    IF ( ISBLANK ( _x ), _y, _x )

 

 

If you have any question, please kindly ask here and we will try to resolve it.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

  • gunjan80's avatar
    gunjan80
    Microsoft Employee
    6 years ago

     It worked perfectly when the date table was not connected. But unfortunatly I need the date table to be connected. My original model has lots of tables along with a date table. And I need the date table to be connected for various other calculations. The line highlighted in yelow is what I need.  Thanks a lot for all the time and effort you've taken.

     

    • v-zhenbw-msft's avatar
      v-zhenbw-msft
      Community Support
      6 years ago

      Hi gunjan80 ,

       

      Sorry for that if a relationship is established, only quarters with data will be displayed.

      Maybe you can create a new date table and create a relationship with original table to do some calculations.

       

      Or you can create four Measures to calculate quarter sales separately.

       

      Q1 = 
VAR _maxQ =
    CALCULATE (
        MAX ( Table1[Quarter] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Customer Name] = MAX ( Table1[Customer Name] )
        )
    )
RETURN
    IF (
        "Q1" <= _maxQ,
        CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Quarter] = "Q1" ) ),
        CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Quarter] = _maxQ ) )
    )

       

      Q2 = 
VAR _maxQ =
    CALCULATE (
        MAX ( Table1[Quarter] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Customer Name] = MAX ( Table1[Customer Name] )
        )
    )
RETURN
    IF (
        "Q2" <= _maxQ,
        CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Quarter] = "Q2" ) ),
        CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Quarter] = _maxQ ) )
    )

       

      Q3 = 
VAR _maxQ =
    CALCULATE (
        MAX ( Table1[Quarter] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Customer Name] = MAX ( Table1[Customer Name] )
        )
    )
RETURN
    IF (
        "Q3" <= _maxQ,
        CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Quarter] = "Q3" ) ),
        CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Quarter] = _maxQ ) )
    )

       

      Q4 = 
VAR _maxQ =
    CALCULATE (
        MAX ( Table1[Quarter] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Customer Name] = MAX ( Table1[Customer Name] )
        )
    )
RETURN
    IF (
        "Q4" <= _maxQ,
        CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Quarter] = "Q4" ) ),
        CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Quarter] = _maxQ ) )
    )

       

       

       

      If you have any question, please kindly ask here and we will try to resolve it.

       

      Best regards,

       

      Community Support Team _ zhenbw

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

      BTW, pbix as attached.

      • gunjan80's avatar
        gunjan80
        Microsoft Employee
        6 years ago

        I fixed this. Here is the DAX.

         

        Last Sale Amount2 = CALCULATE(sum(Table1[Sales]), All('Date'[Quarter]), CALCULATETABLE(LASTDATE(Table1[Date]),ALLEXCEPT(Table1, 'Table1'[Customer Name]) ))