Forum Discussion

ahhk2000's avatar
ahhk2000
Copper Contributor
Aug 13, 2021

DAX time comparison function help

Would be grateful for advice on how to calculate the following with a DAX formula & Power Pivot (Excel 2016)

 

I have a sheet with three columns: Date (in DD/MM/YYYY) | Name of Sales Person | Sale Amount

 

I'm trying to calculate the percentage change in total sale amount between two periods (for example last 6 months vs. prior 6 months).  Any suggestions appreciated. 

BI & Data Analysis
excel
Formulas and Functions

4 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 13, 2021

    ahhk2000 

    First you need to have dates table (as variant could be created within Power Pivot) linked to Sales

    DAX measures could be as

    Sales 6 months:=VAR endDate=MAX('Calendar'[Date])
VAR startDate=EDATE(endDate,-6)
RETURN
    CALCULATE (
        SUM ( Table1[Sales] ),
        ALL ( 'Calendar' ),
            ('Calendar'[Date] >= startDate
           && 'Calendar'[Date] <= endDate
   ))

    if in simplest case to define periods manually within measures. For another 6 months

    Sales Prev 6 months:=VAR endDate=EDATE(MAX('Calendar'[Date]),-6)
VAR startDate=EDATE(endDate,-13)+1
RETURN
    CALCULATE (
        SUM ( Table1[Sales] ),
        ALL ( 'Calendar' ),
            ('Calendar'[Date] >= startDate
           && 'Calendar'[Date] <= endDate
   ))

    Please check in attached.

    • ahhk2000's avatar
      ahhk2000
      Copper Contributor
      Aug 16, 2021
      Sergei, thank you so much for your help. I shoud have clarified that I meant 6 months from present, so I got it working with Today () instead of using the MAX function for endDate in your example. Very helpful.

      I'm wondering for a related scenario - if there are duplicate entries and a 4th field for "Tranasaction ID" (e.g. more than one sales person involved in the same sale). Is there a function for the sum of total amount based on distinct Transaction ID only?






  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Aug 13, 2021

    ahhk2000

    Here is an older proposal for a solution without a DAX formula, it may help in your project.

    If not, please ignore. Example with simple formula inserted in the file.

     

    Thank you for your understanding and patience

     

    Wish you a nice day.

     

    Hope I was able to help you with this information.

     

    Nikolino

    I know I don't know anything (Socrates)

Resources