Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
EParks19
Frequent Visitor

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 DateThis MonthYearDivisionUnitL25L27
10/31/2019October2019IGRT 111,002.451,193,019.82
10/31/2019October2019IGRT 12720,894.01224,147.90
10/31/2019October2019IGRT 1390,466.11196,519.57
10/31/2019October2019IGRT 21368,732.38195,284.46
10/31/2019October2019IGRT 22385,598.15159,559.51
10/31/2019October2019IGRT 23350,836.39127,410.85
10/31/2019October2019IGRT 24336,676.40183,447.83
10/31/2019October2019IGRT 25349,980.59180,947.13
10/31/2019October2019IGRT Aud684.00167.84
10/31/2019October2019IGRT Con2,294.600.00
10/31/2019October2019IGRT Est920.7664.99
10/31/2019October2019IGRT Int1,329.820.00
10/31/2019October2019IGRT Non470.90138.46
10/31/2019October2019IGRT Xxo CA0.000.00
10/31/2019October2019IGRT Xxo non ca0.000.00
11/30/2019November2019IGRT 111,820.952,416,572.56
11/30/2019November2019IGRT 121,400,575.25413,473.48
11/30/2019November2019IGRT 13172,115.96435,551.03
11/30/2019November2019IGRT 11718,655.17339,460.79
11/30/2019November2019IGRT 12747,455.85299,947.39
11/30/2019November2019IGRT 13680,415.77243,762.29
11/30/2019November2019IGRT 14643,439.04373,607.68
11/30/2019November2019IGRT 15664,072.43323,873.47
11/30/2019November2019IGRT Aud755.42167.84
11/30/2019November2019IGRT Con4,051.600.00
11/30/2019November2019IGRT Est631.0424.99
11/30/2019November2019IGRT Int2,072.280.00
11/30/2019November2019IGRT Non0.00276.92
11/30/2019November2019IGRT Xxo CA0.000.00
11/30/2019November2019IGRT Xxo non ca0.000.00
11/30/2019November2019IGRT OBS0.000.00

 

I need to get to this somehow:

Report DateDivisionDistributions
10/31/2019IGR$   5,070,594.92
11/30/2019IGR$   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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

@EParks19 , 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
Not applicable

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
Not applicable

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.

 

result table.png

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
Not applicable

Agree 100% ! This is an amazing community!

Bizualisation
Resolver I
Resolver I

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!

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

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 🙂

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.