Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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    ...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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