Forum Discussion

Zaibass81's avatar
Zaibass81
Helper I
6 years ago
Solved

sum every nth and insert blank

Hi
Looking for a solution on the example below, basically on the right side is the desired output was depending on type A or B adding monthly qty's then inserting blank and continues summing.
Any help more than appreciated.

 

 

 

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Zaibass81 ,

     

    We can create a measure as below to work on it.

    Measure = 
VAR ty =
    MAX ( data[Type] )
VAR mon =
    MONTH ( MAX ( 'data'[DATE] ) )
VAR pre = mon - 1
VAR next = mon + 1
VAR m2 =
    MOD ( mon, 2 )
RETURN
    IF (
        ty = "A"
            && m2 = 1,
        SUM ( data[value] )
            + CALCULATE (
                SUM ( data[value] ),
                FILTER ( ALLEXCEPT ( data, data[Category] ), MONTH ( 'data'[DATE] ) = next )
            ),
        IF (
            ty = "B"
                && m2 = 0,
            SUM ( data[value] )
                + CALCULATE (
                    SUM ( data[value] ),
                    FILTER ( ALLEXCEPT ( data, data[Category] ), MONTH ( 'data'[DATE] ) = pre )
                )
        )
    )

     

    For more details, please check the pbix as attached.

     

6 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    Can you provide actual data in a table or linked Excel file from OneDrive, and explain what it is you are wanting? At a glance, I cannot tell what the yellow and red things are doing. Provide explicit details on how to get from the left side to the right side.

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Zaibass81 ,

     

    We can create a measure as below to work on it.

    Measure = 
VAR ty =
    MAX ( data[Type] )
VAR mon =
    MONTH ( MAX ( 'data'[DATE] ) )
VAR pre = mon - 1
VAR next = mon + 1
VAR m2 =
    MOD ( mon, 2 )
RETURN
    IF (
        ty = "A"
            && m2 = 1,
        SUM ( data[value] )
            + CALCULATE (
                SUM ( data[value] ),
                FILTER ( ALLEXCEPT ( data, data[Category] ), MONTH ( 'data'[DATE] ) = next )
            ),
        IF (
            ty = "B"
                && m2 = 0,
            SUM ( data[value] )
                + CALCULATE (
                    SUM ( data[value] ),
                    FILTER ( ALLEXCEPT ( data, data[Category] ), MONTH ( 'data'[DATE] ) = pre )
                )
        )
    )

     

    For more details, please check the pbix as attached.

     

    • Zaibass81's avatar
      Zaibass81
      Helper I
      6 years ago

      That's exacly what i am looking for, however, i was looking to do it with power query, or DAX , not with BI, as i am not using it.

       

      Thanks,

       

      T.

      • edhans's avatar
        edhans
        Community Champion
        6 years ago

        Zaibass81 Power BI is a product that uses Power Query to extract, transform, and load data, then DAX to analyze the data. So when you say you want to do with with DAX or Power Query but not BI, that doesn't make sense. 

         

        If you mean you want to do it in Excel using Power Query or DAX (via Power Pivot) then unless the solution provided uses some newer DAX functions (those from 2018 or later), it will work fine there too.

    • Zaibass81's avatar
      Zaibass81
      Helper I
      6 years ago

      Hi,
      just another quick question, what i need to change in the measure below, to sum in 3 monthly buckets i.e. category A Jan+Feb+Mar (=6) instead of what it's doing at the moment every 2 monthly buckets (Jan+feb =3)

       

      Thanks in advance