Forum Discussion
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 BI, add my new reports and let the refresh take care of all the work.
- First, I have to add 2 columns together (L25 & L27) to get a total figure for each unit.
- I then have to add all the units together to get a single figure for the division for the month.
- If it's not the first month of the fiscal year (October), I need to take the current month's ytd figure and subtract the prior month's ytd figure to get the current monthly amount.
My data looks like this:
| Report Date | This Month | Year | Division | Unit | L25 | L27 |
| 10/31/2019 | October | 2019 | IGR | T 11 | 1,002.45 | 1,193,019.82 |
| 10/31/2019 | October | 2019 | IGR | T 12 | 720,894.01 | 224,147.90 |
| 10/31/2019 | October | 2019 | IGR | T 13 | 90,466.11 | 196,519.57 |
| 10/31/2019 | October | 2019 | IGR | T 21 | 368,732.38 | 195,284.46 |
| 10/31/2019 | October | 2019 | IGR | T 22 | 385,598.15 | 159,559.51 |
| 10/31/2019 | October | 2019 | IGR | T 23 | 350,836.39 | 127,410.85 |
| 10/31/2019 | October | 2019 | IGR | T 24 | 336,676.40 | 183,447.83 |
| 10/31/2019 | October | 2019 | IGR | T 25 | 349,980.59 | 180,947.13 |
| 10/31/2019 | October | 2019 | IGR | T Aud | 684.00 | 167.84 |
| 10/31/2019 | October | 2019 | IGR | T Con | 2,294.60 | 0.00 |
| 10/31/2019 | October | 2019 | IGR | T Est | 920.76 | 64.99 |
| 10/31/2019 | October | 2019 | IGR | T Int | 1,329.82 | 0.00 |
| 10/31/2019 | October | 2019 | IGR | T Non | 470.90 | 138.46 |
| 10/31/2019 | October | 2019 | IGR | T Xxo CA | 0.00 | 0.00 |
| 10/31/2019 | October | 2019 | IGR | T Xxo non ca | 0.00 | 0.00 |
| 11/30/2019 | November | 2019 | IGR | T 11 | 1,820.95 | 2,416,572.56 |
| 11/30/2019 | November | 2019 | IGR | T 12 | 1,400,575.25 | 413,473.48 |
| 11/30/2019 | November | 2019 | IGR | T 13 | 172,115.96 | 435,551.03 |
| 11/30/2019 | November | 2019 | IGR | T 11 | 718,655.17 | 339,460.79 |
| 11/30/2019 | November | 2019 | IGR | T 12 | 747,455.85 | 299,947.39 |
| 11/30/2019 | November | 2019 | IGR | T 13 | 680,415.77 | 243,762.29 |
| 11/30/2019 | November | 2019 | IGR | T 14 | 643,439.04 | 373,607.68 |
| 11/30/2019 | November | 2019 | IGR | T 15 | 664,072.43 | 323,873.47 |
| 11/30/2019 | November | 2019 | IGR | T Aud | 755.42 | 167.84 |
| 11/30/2019 | November | 2019 | IGR | T Con | 4,051.60 | 0.00 |
| 11/30/2019 | November | 2019 | IGR | T Est | 631.04 | 24.99 |
| 11/30/2019 | November | 2019 | IGR | T Int | 2,072.28 | 0.00 |
| 11/30/2019 | November | 2019 | IGR | T Non | 0.00 | 276.92 |
| 11/30/2019 | November | 2019 | IGR | T Xxo CA | 0.00 | 0.00 |
| 11/30/2019 | November | 2019 | IGR | T Xxo non ca | 0.00 | 0.00 |
| 11/30/2019 | November | 2019 | IGR | T OBS | 0.00 | 0.00 |
I need to get to this somehow:
| Report Date | Division | Distributions |
| 10/31/2019 | IGR | $ 5,070,594.92 |
| 11/30/2019 | IGR | $ 4,812,184.28 |
I've been all over the place with creating measures and calculations to the point that I am totally lost on this one.
Any help would be so greatly appreciated.
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
8 Replies
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
A little tricky to do this in the abstract but here is my approach
- Make a calculated column to get your total (L25 + L27) and call it, for example, Total
- Make a measure for total sales, like Sum(Total). It doesn't matter if you have multiple units you want to split out, as you can do that using row context in your table later
- Lastly I'm not sure you need to mess around with YTD calculations - if you place the measure in a table with Date and Unit you should get what you are after, assuming that your data is not cumulative, which it doesn't look like it is?
Hope this helps!
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 🙂
Anonymous , with help from date table and time intelligence
MTD Sales = CALCULATE(SUM(Sales[L25]+SUM(Sales[L27]),DATESMTD('Date'[Date])) last MTD Sales = CALCULATE(SUM(Sales[L25]+SUM(Sales[L27]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) or last MTD Sales = CALCULATE(SUM(Sales[L25]+SUM(Sales[L27]),dateadd('Date'[Date],-1,MONTH)) Current Month = [MTD Sales] -[last MTD Sales]To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
