Dax Sum Help

[Anonymous]

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!

[parry2k]

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

[Anonymous]

@parry2k 

Hi 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"

[mahoneypat · Accepted solution]

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

[Anonymous]

Hi @mahoneypat ,

If i have different Products, how would this logice work the same for it?

Products = Sweets / Clothes / Food

Each product needs to be calucalted by a certain detail i.e

Sweets ACT = Gum & Lolly

Clothes ACT = Shoes & Trousers

Rolling totals should be everything. I notices the Or operator is only for 2 arguments.

Could you advise?

Thank you for your help so far!

[mahoneypat]

Two suggestions for you:

1.  You could use a Groups column to add a column that has the values of Sweets, Clothes, etc.  You could then use that new column in measures, in a slicer, etc. Please see this link - https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning

2.  If you have a slicer with the Detail column, instead of the OR() you can use VALUES(Sweets[Detail]) in its place.  Your requirement to only have those two fields for the March calculation complicates things, but the previous measure should work with VALUES() there.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat