How to get This Month sales Amount ?

[Paulyeo11]

Hi All

I have below expression working fine :-

This Year = CALCULATE(sum('order'[Price]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))

This Week = CALCULATE(sum('order'[Price]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

Can some one share with me This month and last month expression ?

Paul

[amitchandak]

@Paulyeo11 , for month, create a rank on the month start date

New column in date table

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)

measure
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

for Qtr

Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

refer how to create these start dates in my calender video series or this blog

My Video Series ,

1.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

[Paulyeo11]

Hi Amit

Thank you for your help.

My raw data have current month data :-

When i use your expression , no error , but return null value :-

My PBI file :-

https://www.dropbox.com/s/b939tr67qc3g8qu/preWebinarPostCal_PY_DATA%20V003.pbix?dl=0

Hope you can let me know where go wrong ?

Paul

[v-yingjl · Accepted solution]

Hi @Paulyeo11 ,

Based on the picuture, the desired result of this month should be 40 and result of last month is blank if I got it correctly.

Create measure like this:

_this month = 
CALCULATE (
    SUM ( 'order'[Price] ),
    FILTER (
        ALL ( 'Date' ),
        MONTH ( 'Date'[Date] )
            = MONTH (
                CALCULATE (
                    MAX ( 'SI_PM_'[AR Invoice Date] ),
                    ALLEXCEPT ( SI_PM_, SI_PM_[SOURCE] )
                )
            )
    )
)

_previous month = 
CALCULATE (
    SUM ( 'order'[Price] ),
    FILTER (
        ALL ( 'Date' ),
        MONTH ( 'Date'[Date] )
            = MONTH (
                CALCULATE (
                    MAX ( 'SI_PM_'[AR Invoice Date] ),
                    ALLEXCEPT ( SI_PM_, SI_PM_[SOURCE] )
                )
            ) - 1
    )
)

Check the modified file in the below, hopes to help you. If not help, please feel free to let me know.

Best Regards,
Yingjie Li

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

[Paulyeo11]

Hi Ying

thank you very much for your effect.

paul