Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help with calculations

I need to create a line chart with monthl by month figures.  I get a report each month that I need to complete multiple steps in Excel to create this chart.  I want to be able to create this in Power...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi,

    I did this in two measures. Hope this helps!

    UnitTotal = SUMX('Table', 'Table'[L25]+'Table'[L27])

Monthly Total = 
VAR PrevMonthUnitTotal = CALCULATE([UnitTotal],
                         PREVIOUSMONTH('Table'[Report Date])
                         )
Return
IF(SELECTEDVALUE('Table'[This Month]) = "October",
    [UnitTotal],
    [UnitTotal]-PrevMonthUnitTotal
    
 )

     

    ~Kim

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you for such a quick response.  Unfortunately, my data is cumulative.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Not a problem!

 

We'll just need to make a extra measures. First we want the YTD total, but with October being the start so make a measure like this:

 

YTD = TOTALYTD(SUM([Total], [Date], All ([Date]), "01/10")

 

This will give you a YTD measure. Next we need to figure out the YTD for the previous month

 

PYTD = TOTALYTD(SUM([Total], [Date], PREVIOUSMONTH([Date]), "01/10")

 

Now make a final measure which is just the first measure - the second measure

 

Final = [YTD] - [PYTD]

 

Pop that in your table with your unit and your date and it should be fine 🙂