Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Monthly Comparison v January with True Total

Hi 

 

I am new to Power Bi but need some help, please?

 

I have a measure to produce Col B on a filtered set of data. This seems to work ok and produces the expected results with an expected total.

 

Total Revenue = SUM ('Live Update Sheet'[Monthly Revenue])
 
What I'd like to do in the table visualisation is produce Cols C & D below.
 
Col C, Add January;s Total Revenue to all rows and give an expected total?
Col D Compare the difference with Col C - Col B and give the expected total?
 
Appreciate any help on this this! 

 

ABCD
MonthNameTotal RevenueCompare JanRev v Jan
January $         122,425 $      122,425 $            -  
February $            96,697 $      122,425 $   25,728
March $         150,986 $      122,425 $  -28,561
April $         125,859 $      122,425 $    -3,434
May $         120,779 $      122,425 $     1,646
June $         145,404 $      122,425 $  -22,979
July $         108,669 $      122,425 $   13,756
August $         103,346 $      122,425 $   19,079
September $         104,509 $      122,425 $   17,916
October $            83,507 $      122,425 $   38,918
November $            84,750 $      122,425 $   37,675
December $            88,112 $      122,425 $   34,313
  $      1,335,043 $  1,469,100 $ 134,057

 

Regards

 

Alex

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Anonymous 

     

    In case if you want to show revenue by just month and year but there are days in dates.
     
    You can also change the Jan revenue to the following:
    Jan Revenue =CALCULATE(SUM('Live Update Sheet'[Monthly Revenue]), Date[Date].[Year]= 2020, Date[Date].[Month.No] = 01))
     
     
    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    Two measures seem to be giving the desired result
     
    Revenue 01 Baseline = CALCULATE(SUM('Live Update Sheet'[Monthly Revenue]),Dates[Date] = Date (2020,1,01))
     
    and
     
    Revenue 02 Baseline = IF(
    HASONEVALUE(Dates[NoMonth]), [Revenue 01 Baseline],
    SUMX(VALUES(Dates[NoMonth]),
    [Revenue 01 Baseline])
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Anonymous 

     

    In case if you want to show revenue by just month and year but there are days in dates.
     
    You can also change the Jan revenue to the following:
    Jan Revenue =CALCULATE(SUM('Live Update Sheet'[Monthly Revenue]), Date[Date].[Year]= 2020, Date[Date].[Month.No] = 01))
     
     
    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for that! Useful.