sum every nth and insert blank

[Zaibass81]

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.

[v-frfei-msft · Accepted solution]

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]

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

[Zaibass81]

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]

@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]

Ok, perhaps my english.. however, what i ment is that i am looking for a solution in M language which is uesed by power query.

Rgds,

T

[edhans]

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.