Help with calculations

[Anonymous]

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.

[amitchandak]

@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/

[Anonymous · Accepted solution]

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]

it looks like some of the other replies are calculating YTD amounts, but based on what you entered, it looks like it's already YTD and you're just trying to calculate the monthly amounts - right?

This is what I came out with.

[Anonymous]

I want to thank you both for all the help today and so quickly.  This is a remarkable community and I'm learning so much from all of you.  I hope to pay it forward in the future.  Again, Thank you!

[Anonymous]

Agree 100% ! This is an amazing community!

[Anonymous]

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!

[Anonymous]

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

[Anonymous]

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 🙂