Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

PREVIOUS MONTH not working

Hi everyone,   I've been browsing the forums forquite a while but still cannot soolve my problem. I need to build a simple measure showing previous month sales. The standard DAX formula prevMonth =...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
7 years ago

Anonymous ,

 

This issue is caused by quantity and date are in different tables. So the two tables have relatioship, right? Suppose in fact table date column is named as _date. Then you can build a measure in fact table using DAX like pattern below:

 

prevMonth =
VAR current_month =
    MONTH ( Fact_Table[_date] )
VAR previous_month = current_month - 1
RETURN
    CALCULATE (
        SUM ( [quantity] ),
        FILTER ( Fact_Table, [_date] = previous_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

Thanks for such an elegant solution v-yuta-msft!

 

While typing your code in the MSBI Desktop, realized that my factSales table doesn't have any date colum as such, instead I created dateKey integer column 20190221 which connects it to the date table. Which, in turn, along with the dateKey column has real date one. Therefore, I am afraid that wouldn't work.

 

Would you kindly comment on that?