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 = CALCULATE(SUM([quantity]);PREVIOUSMONTH(dimDate[date]))  doesn't seem to be working showing NULL against each dateKey column.

 

My dimDate table isn't contiguous, it has only first day of each month as in factSales all sales are aggregated on one day only (first day of each month).

 

 

Tried other CALCULATE variations with PARALLELPERIOD, but they also didn't show any results.

Need Help

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community 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?