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...
  • 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

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