Forum Discussion
Dax Sum Help
Hi Guys,
I am trying to calculate a sum of everymonth based from the original month.
Jan to Apr =
Calculate(Sum(Value),Sweets="Gum", Cat="lollipop")
| Jan | Feb | Mar | Apr | |
| Sweets | 27161 | 22894 | 58176 | 29405 |
What i want is the next months are forecast. So i would like to create a measure that would add on to Aprils 29405 and be rolling from then on.
| May | June | July | August | |||
| Forecast Data | 100 | 200 | 150 | 40 | ||
| Desired | 29505 | 29705 | 29855 | 29895 | ||
| How | 29405+100 | 29505+200 | 29705+150 | 29855+40 |
The Forecast would need to include all sweets and not just Gum & lollipops
Happy to have this in seperate table. I am trying to add the forecast numbers to the Final number in April, and to have it rolling, but it starts from the final actual in April
Thank you in advance!
That was a good way to share source data. I wish more people did it like that. Here is an expression that gets your desired output. I added a column to your data to make a month column for the visual, but the expression doesn't use it.
Rolling =
VAR currentdate =
IF ( HASONEVALUE ( Sweets[Date] ), MIN ( Sweets[Date] ) )
VAR thismonthtotal =
CALCULATE (
SUM ( Sweets[GBP] ),
OR ( Sweets[Detail] = "Gum", Sweets[Detail] = "Lolly" )
)
VAR marchtotal =
CALCULATE (
SUM ( Sweets[GBP] ),
OR ( Sweets[Detail] = "Gum", Sweets[Detail] = "Lolly" ),
ALL ( Sweets ),
Sweets[Date] = DATE ( 2020, 3, 1 )
)
VAR rollingtotal =
CALCULATE (
SUM ( Sweets[GBP] ),
ALL ( Sweets ),
Sweets[Date] <= currentdate,
Sweets[Date] >= DATE ( 2020, 4, 1 )
)
RETURN
IF (
currentdate >= DATE ( 2020, 4, 1 ),
rollingtotal + marchtotal,
thismonthtotal
)If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
5 Replies
Anonymous how does your raw data look like? Share sample pbix file with Raw data. Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Hi Please see below for sample data code.
I essentialy want to calculate a rolling total after March. I want March's Figures to be made up of only Gum & Lolly, so Calculate(Sum(GBP),Detail="Gum",Detail = "Lolly")
However going forward it needs to be a cumulative rolling total from March's final figure for all detail lines.
In the data March will have a total of 19. So in the new measure April should be (299+19) and May should be (318+116)
Like this:
Jan Feb Mar Apr May Sweet 50 197 19 318 434 Can this be done in one measure?
Here is the M code for some sample data...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5PTS1R0lEyMNQFIiMDIwMgxxBEuJfmAknH5BKlWB0c6kxAhE9+Tk4lDpVGMJXmZsSqhNmdm5laTEitkSGxphpZ4vURXJ0JEAdnZ5aU5BBpOxFmGhLvShOiQ8mUgEpjuEr8PjeBqQMpc07MSwEZ6IZPoTkiceBVZ2lGrImmyJ7Bq9LEmDi7jQjZbQpTaWyJ10RTlGRJlIlm5qgmxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t, GBP = _t, Detail = _t, Type = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Date", type date}, {"GBP", Int64.Type}, {"Detail", type text}, {"Type", type text}}) in #"Changed Type"That was a good way to share source data. I wish more people did it like that. Here is an expression that gets your desired output. I added a column to your data to make a month column for the visual, but the expression doesn't use it.
Rolling =
VAR currentdate =
IF ( HASONEVALUE ( Sweets[Date] ), MIN ( Sweets[Date] ) )
VAR thismonthtotal =
CALCULATE (
SUM ( Sweets[GBP] ),
OR ( Sweets[Detail] = "Gum", Sweets[Detail] = "Lolly" )
)
VAR marchtotal =
CALCULATE (
SUM ( Sweets[GBP] ),
OR ( Sweets[Detail] = "Gum", Sweets[Detail] = "Lolly" ),
ALL ( Sweets ),
Sweets[Date] = DATE ( 2020, 3, 1 )
)
VAR rollingtotal =
CALCULATE (
SUM ( Sweets[GBP] ),
ALL ( Sweets ),
Sweets[Date] <= currentdate,
Sweets[Date] >= DATE ( 2020, 4, 1 )
)
RETURN
IF (
currentdate >= DATE ( 2020, 4, 1 ),
rollingtotal + marchtotal,
thismonthtotal
)If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
