Finding sales for last 60 days

DAX DAX Calculations
1

Hi All,

I need help in writing the measure. It is the sum of sales for month of June (6/2020).
The filter on the measure should be :-
Bill paid date within 60 days,
type= “PQR”,
Region not equal to “unknown”.

Below is the data set.
|Extraction Date |Sale AMT |Type |region |Bill paid date|
|6/22/2020 |$20.00 |abc |ASIA |6/31/2020|
|6/23/2020 | $13.00 |xyz |EU |6/29/2020|
|6/30/2020 | $13.00 |PQR |EU |6/25/2020|
|6/1/2020 | $35.00 |PQR |NA |6/30/2020|
|6/2/2020 | $37.67 |PQR |NA |5/30/2019|
|6/3/2020 | $45.17 |PQR |NA |6/25/2020|
|6/4/2020 | $52.67 |PQR |NA |5/23/2020|
|5/12/2020 | $19.00 |abc |EU |3/10/2020|
|6/13/2020 | $20.00 |PQR |EU |5/22/2020|
|6/20/2020 | $88.73 |PQR |EU |2/2/2020|
|5/1/2020 | $60.46 |PQR |EU |1/15/2020|
|6/3/2020 | $157.67 |xyz |NA |3/1/2020|
|4/5/2020 | $19.00 |PQR |Unknown|2/2/2020|
|6/15/2020 | $49.11 |xyz |NA |3/1/2020|
|4/27/2020 | $41.17 |xyz |NA |5/1/2020|
|4/29/2020 | $39.84 |PQR |EU |6/2/2020|

Thank you

2

try this out—
DAX 60 Days = CALCULATE (
SUM ( (Table[Sale AMT]),Table[date]<=Today()-60),
FILTER ( Table, Table[region] = ‘Unknown’ )
)

3

Hello @krajbhandari3,

Thank You for posting your query onto the Forum.

You can try out the formula given below -

Sales in Last 60 Days = 
VAR Last_Date = LASTDATE( Sales[Bill Paid Date] )

RETURN
CALCULATE( [Total Sales] , 
    FILTER( ALL( Dates ) , 
        Dates[Date] > Last_Date - 60 && 
        Dates[Date] < Last_Date ) , 
    FILTER( Sales , Sales[Type] = "PQR" ) ,
    FILTER( Sales , Sales[Region] = "Unknown" ) )

Hoping this formula works for you. :slightly_smiling_face:

Thanks & Warm Regards,
Harsh

4

Hi @krajbhandari3! Welcome to the Forum! Hope you are having a great experience so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

5

Thank you @Harsh for providing a quick reply!

6

Thank you All,

  1. DAX 60 Days = CALCULATE (
    SUM ( (Table[Sale AMT]),Table[date]<=Today()-60),
    FILTER ( Table, Table[region] = ‘Unknown’ )
    )
  2. Sales in Last 60 Days =
    VAR Last_Date = LASTDATE( Sales[Bill Paid Date] )

RETURN
CALCULATE( [Total Sales] ,
FILTER( ALL( Dates ) ,
Dates[Date] > Last_Date - 60 &&
Dates[Date] < Last_Date ) ,
FILTER( Sales , Sales[Type] = “PQR” ) ,
FILTER( Sales , Sales[Region] = “Unknown” ) )

Dax number 2 worked.
Thank you