Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Display Last Month Sales in KPI box

hi all,   I am looking for 3 simple calculations including instructions on how to display them in a KPI box.   1. Last month revenue 2. Avg. last 3 months revenue 3. Avg. launch-to-date revenue...
Need Help
v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi Anonymous,

 

I made one sample here for your reference. We can create the mesaures as below to meet your requirement.

 

Last month revenue = IF(MONTH(MAX(Table1[date]))<>12,CALCULATE(SUM(Table1[sales]),FILTER(ALL(Table1),YEAR(Table1[date])*100+MONTH(Table1[date]) = YEAR(MAX(Table1[date]))*100 +MONTH(MAX(Table1[date]))-1)),CALCULATE(SUM(Table1[sales]),FILTER(ALL(Table1),YEAR(Table1[date])*100+MONTH(Table1[date]) = YEAR(MAX(Table1[date])-1)*100 +01)))
Avg. last 3 months revenue = CALCULATE(SUM(Table1[sales]),DATESINPERIOD(Table1[date],LASTDATE(Table1[date]),-3,MONTH))/3
Avg. launch-to-date revenue = CALCULATE(SUM(Table1[sales]),ALL(Table1))/CALCULATE(DISTINCTCOUNT(Table1[yyyymm]),ALL(Table1))

Then we can get the result as we excepted.

 

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/3k9zlqncgahytp7/Display%20Last%20Month%20Sales%20in%20KPI%20box.pbix?dl=0

 

Regards,

Frank

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi Anonymous,

     

    Does that make sense? If so, kindly mark my answer as a solution to close the case.

     

    Regards,

    Frank

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi v-frfei-msft,

       

      Thanks for your reply.

       

      I have been trying to replicate your formula using my tables, unfortunately there is some syntax error.

       

      Please see attached pictures.

       

      I noticed a difference, I have repeating same dates and you only have 1 date per X amount of sale.

       

      Could that cause the problems?

       

       

       

       

       

       

       

      best regards