Forum Discussion

PavelP's avatar
PavelP
Frequent Visitor
8 years ago
Solved

Report automation

Hi guys,

 

I have a problem with process automation in Power BI. For my report and for this case I have two datasets:

 

- ACTUAL sales values (Jan - not complete July)

- Latest view sales values - forecast values for the rest of the July.

 

I use this formula

CALCULATE(Sales_NetSuite[Sales(ACT)],'Calendar'[Month] IN {"Jan", "Feb", "Mar","Apr","May","Jun","Jul"})+CALCULATE(SUM('Latest view_FC'[Sales Orders - Latest view]),'calendar'[Month] IN {"Jul"})

 

With this setup I have to make manual update of this formula for next month = I must manually add "Aug".

 

Can you help me guys how to correct this formula to be fully automatized without manual updates.

 

Thank you for your help

  • Anonymous's avatar
    Anonymous
    8 years ago

    PavelP,

    Please create a MonthNumber column in your calendar table if you don't have the column, then check if one of the following DAX returns your expected result.

    Measure  = VAR CURRENTMONTH= FORMAT(NOW(),"MMM") RETURN CALCULATE(SUM('Latest view_FC'[Sales Orders - Latest view]),'Calendar'[Month]=CURRENTMONTH)+CALCULATE(Sales_NetSuite[Sales(ACT)],'Calendar'[MonthNumber] <=MONTH(NOW()) && 'Calendar'[MonthNumber]>=1)

     

    Measure1 = CALCULATE(SUM('Latest view_FC'[Sales Orders - Latest view],'Calendar'[MonthNumber] =MONTH(NOW())-1)+CALCULATE(Sales_NetSuite[Sales(ACT)],'Calendar'[MonthNumber] <=MONTH(NOW())-1 && 'Calendar'[MonthNumber] >=1)




    Regards,

     

    Lydia

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    PavelP,

    Please create a MonthNumber column in your calendar table if you don't have the column, then check if one of the following DAX returns your expected result.

    Measure  = VAR CURRENTMONTH= FORMAT(NOW(),"MMM") RETURN CALCULATE(SUM('Latest view_FC'[Sales Orders - Latest view]),'Calendar'[Month]=CURRENTMONTH)+CALCULATE(Sales_NetSuite[Sales(ACT)],'Calendar'[MonthNumber] <=MONTH(NOW()) && 'Calendar'[MonthNumber]>=1)

     

    Measure1 = CALCULATE(SUM('Latest view_FC'[Sales Orders - Latest view],'Calendar'[MonthNumber] =MONTH(NOW())-1)+CALCULATE(Sales_NetSuite[Sales(ACT)],'Calendar'[MonthNumber] <=MONTH(NOW())-1 && 'Calendar'[MonthNumber] >=1)




    Regards,

     

    Lydia

    • PavelP's avatar
      PavelP
      Frequent Visitor
      8 years ago

      Hi Lydia,

       

      it looks that everything is working perfectly!!!!!

       

      Great!!!! Thank you very much!

       

      Pavel

  • PavelP's avatar
    PavelP
    Frequent Visitor
    8 years ago

    Hi guys,

     

    I have a problem with process in Power Bi. For my project and for this case I have two datasets:

     

    - ACTUAL sales values (Jan - non complete Jul)

    - Latest view sales values (forecast for the rest of the July)

     

    I use this formula

    CALCULATE(Sales_NetSuite[Sales(ACT)],'Calendar'[Month] IN {"Jan", "Feb", "Mar","Apr","May","Jun","Jul"})+CALCULATE(SUM('Latest view_FC'[Sales Orders - Latest view]),'calendar'[Month] IN {"Jul"})

     

    With this formula I have to make manual update for the next month = I must add "Aug"

     

    Can you guys help me how to correct this formula to by fully automatized without manual updates for the next months?

     

    Thank you very much for your help.

     

    Pavel