Current Year Previous Month DAX

[prasy14]

Hello All,

I am not getting correct t calculated value for previous Month column , For example in below screen shot CM sales for March =3,881,693. in PM Sales column of April month i am expecting 3,881,693.

CM Sales = CALCULATE(SUM('sales'[salesamt]), bcalendar[byear]= YEAR(TODAY()))-- Current Year Sales by month

PM Sales = CALCULATE (SUM('sales'[salesamt]),PREVIOUSMONTH(bcalendar[bdate]), bcalendar[biyear]= YEAR(TODAY())) ---Current Year Previous Month Sales

 

[v-yulgu-msft · Accepted solution]

Hi @prasy14,

Please try this:

PM Sales =
CALCULATE (
    SUM ( 'Sales'[SalesAmount] ),
    FILTER (
        ALLSELECTED ( 'bcalendar' ),
        'bcalendar'[bdate].[MonthNo]
            = MONTH ( MAX ( 'bcalendar'[bdate] ) ) - 1
            && 'bcalendar'[byear] = YEAR ( TODAY () )
    )
)

Regards,

Yuliana Gu

[mattbrice]

You can try:

PM Sales = CALCULATE ( [CM Sales],PREVIOUSMONTH(bcalendar[bdate]) ) ---Current Year Previous Month Sales

Though if it were me, i'd have a 'Year' slicer, to make it easier.

[prasy14]

Hello @mattbrice,

i have to display Current Year,Last Year, LLY,LLLY by months as well that is reason i am not using year slicer.

the code which you have provided is giving me blank values.

[mattbrice]

Does your 'bcalendar' table include dates for the prior month? prior year? 

[BILASolution]

Hi @prasy14

This could be useful for you

https://1drv.ms/f/s!AuU-Ye8UGM4Rko0xvebM1ZQ7ZZhDeg

[prasy14]

Hi @BILASolution,

Thanks for PBIX file, I still get blank values when i use the DAX and I do have same relationship between the tables as yours

[BILASolution]

@prasy14
Is possible you can send me your pbix file to find a better solution?
likesilverfrog@hotmail.com