Forum Discussion

RYOH_0130's avatar
RYOH_0130
Regular Visitor
2 years ago
Solved

Difficulty Extracting Previous Month's Data

Dear all,

 

I am currently facing an issue with extracting data from the previous month in PowerBI. I have a column named 'Date_Period_SEQ' that represents the sequence of months(ex.Time1=1,Time2=2,...). I am trying to calculate the 'No. of Buyer' for the previous month based on this sequence. However, my DAX formula seems to be returning incorrect results.

Here is the DAX formula I have been using:

 

PreviousMonth_No.of Buyer
= CALCULATE(
     SUM('Consumer Report'[No. of Buyer]),
     FILTER( 'Consumer Report', 'Consumer Report '[Date_Period_SEQ] = MAX('Consumer Report'[Date_Period_SEQ])-1 ) )

 

Despite using this formula, I am not getting the desired data for the previous month. Could you please help me understand what might be going wrong with this approach? I would greatly appreciate any insights or suggestions to resolve this issue.

Thank you in advance for your help.

Best regards, 
RYO

Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    RYOH_0130 , Have a separate date/period table Joined with your table and then try

     

    PreviousMonth_No.of Buyer
    = CALCULATE(
         SUM('Consumer Report'[No. of Buyer]),
         FILTER( all('Period'), 'Period '[Date_Period_SEQ] = MAX('Period'[Date_Period_SEQ])-1 ) )
     
     
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    RYOH_0130 , Have a separate date/period table Joined with your table and then try

     

    PreviousMonth_No.of Buyer
    = CALCULATE(
         SUM('Consumer Report'[No. of Buyer]),
         FILTER( all('Period'), 'Period '[Date_Period_SEQ] = MAX('Period'[Date_Period_SEQ])-1 ) )
     
     
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s