Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Same Day Last Month Comparison

Hi

 

I have a figure that I count for the month and I want to create a field that will show me the count compared to the same day last month.

 

I am using direct query so some of the suggestions I have already seen don't work with it.

 

Any advice would be much appreciated.

 

Thanks

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Anonymous ,

     

    You could modify the measures like pattern below:

    Total Sales Diff =
VAR First_Day_Current_Month =
    EOMONTH ( Table[Date], -1 ) + 1
VAR Third_Day_Current_Month =
    EOMONTH ( Table[Date], -1 ) + 3
VAR First_Day_Last_Month =
    EOMONTH ( Table[Date], -2 ) + 1
VAR Third_Day_Last_Month =
    EOMONTH ( Table[Date], -2 ) + 3
VAR Total_Sales_Current_Month =
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER (
            Table,
            Table[Date] >= First_Day_Current_Month
                && Table[Date] <= Third_Day_Current_Month
        )
    )
VAR Total_Sales_Last_Month =
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER (
            Table,
            Table[Date] >= First_Day_Last_Month
                && Table[Date] <= Third_Day_Last_Month
        )
    )
RETURN
    Total_Sales_Current_Month - Total_Sales_Last_Month

    Community Support Team _ Jimmy Tao

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

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Sample data and expected output would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

     

    That being said, can you calculate the date like:

     

    Measure = DATE(YEAR([Date]),MONTH([Date])-1,DAY([Date]))

    Something along those lines? Obviously will have to account for January dialing back to December of last year but you could handle that with an IF statement or SWITCH.

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    Anonymous ,

     

    So suppose you want to achieve sales in same day last month, you can create a measure using DAX like pattern below:

    Total Sales =
VAR Same_Day_Last_Month =
    EDATE ( Table[Date], -1 )
RETURN
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER ( Table, Table[Date] = Same_Day_Last_Month )
    )

    Community Support Team _ Jimmy Tao

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    So a very simple example would be if I had a list of dates and a count beside them:

     

    01/01/2019 - 10

    02/01/2019 - 20

    03/01/2019 - 70

    .

    .

    .

    .

    01/02/2019 - 20

    02/02/2019 - 40

    03/02/2019 - 100

     

    So far in February I have 160 and I want to compare that to the first 3 days in january. So my calculation would tell me that I am +60 as there have been 60 more in feb than jan for the same time period.

     

    That is the kind of calculation I am looking for.

     

    Thanks

    • v-yuta-msft's avatar
      v-yuta-msft
      Community Support
      7 years ago

      Anonymous ,

       

      You could modify the measures like pattern below:

      Total Sales Diff =
VAR First_Day_Current_Month =
    EOMONTH ( Table[Date], -1 ) + 1
VAR Third_Day_Current_Month =
    EOMONTH ( Table[Date], -1 ) + 3
VAR First_Day_Last_Month =
    EOMONTH ( Table[Date], -2 ) + 1
VAR Third_Day_Last_Month =
    EOMONTH ( Table[Date], -2 ) + 3
VAR Total_Sales_Current_Month =
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER (
            Table,
            Table[Date] >= First_Day_Current_Month
                && Table[Date] <= Third_Day_Current_Month
        )
    )
VAR Total_Sales_Last_Month =
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER (
            Table,
            Table[Date] >= First_Day_Last_Month
                && Table[Date] <= Third_Day_Last_Month
        )
    )
RETURN
    Total_Sales_Current_Month - Total_Sales_Last_Month

      Community Support Team _ Jimmy Tao

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