Forum Discussion

lgbclick's avatar
lgbclick
New Member
7 years ago
Solved

Total for same day last month

Hi, I am new to Power BI and am trying to create a KPI visualisation showing the number of total enquiries vs the number of enquiries we had for the same day last month.

 

i.e. Today, 24th Sept we have had 1820 enquiries since 1st Sept, I would like to have this compare to the total enquiries for 1st August - 24th August, rather than comparing against the whole previous month.

 

I have already set up my 'Total Enquiries' Measure, I am just unsure on the DAX for 'same day last month'.

 

I hope this makes sense. 

Thanks in advance. 
Lyane 

  • Anonymous's avatar
    Anonymous
    7 years ago

    HI lgbclick

     

    Hope you have the date table in your model. If not, please create and join appropriately .

     

    Create Sales MTD measure for Sep month as follows:

    Sales_MTD=CALCULATE(SUM(Sales_Fact.Sales),DATESMTD(Dates[Date]))

    For AUg Month MTD, it will be in 2 steps:

     

     

    Step 1: 
Sales_LastMonth=CALCULATE(SUM(Sales_Fact.Sales),DATEADD(Dates[Date],-1,MONTH)

Step 2: 
Sales_LMTD=CALCULATE(Sales_LastMonth,DATESMTD(Dates[Date]))

     

    Hope this helps.

     

    Thanks

    Raj

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI lgbclick

     

    Hope you have the date table in your model. If not, please create and join appropriately .

     

    Create Sales MTD measure for Sep month as follows:

    Sales_MTD=CALCULATE(SUM(Sales_Fact.Sales),DATESMTD(Dates[Date]))

    For AUg Month MTD, it will be in 2 steps:

     

     

    Step 1: 
Sales_LastMonth=CALCULATE(SUM(Sales_Fact.Sales),DATEADD(Dates[Date],-1,MONTH)

Step 2: 
Sales_LMTD=CALCULATE(Sales_LastMonth,DATESMTD(Dates[Date]))

     

    Hope this helps.

     

    Thanks

    Raj