Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
John_Dozen34999
Frequent Visitor

Cummulative per period frequency

Hi everyone,

 

Im looking for a solution in dax that the value will only multiple by from the frequency indicated.

Here's my table.

Capture122.JPG

 

 

 

My problem is how to in dax can i have this kind of output if i put it in a trend chart.

 

Here's the target output in table.

Capture12.JPG

 

 

 

 

Thanks in advance!

1 ACCEPTED SOLUTION

Hi @John_Dozen34999 ,

 

According to my understanding , you want to display

Employee1 ——100,  100*2 ,

Employee4 ——300,  300*2 , 300*3 ...

not just display the result

Employee1 ——100,200,   

Employee4 ——300 ,600 ,900 ... right?

Please modify the  Cummulative measure:

Cummulative =
VAR _value =
    IF (
        [Measure] = BLANK (),
        BLANK (),
        SUMX (
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Employee] = MAX ( 'Calendar'[Employee] )
                    && 'Calendar'[Month] <= MAX ( 'Calendar'[Month] )
            ),
            [Measure]
        )
    )
VAR _minValue =
    MINX (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Employee] = MAX ( 'Calendar'[Employee] )
        ),
        [Measure]
    )
RETURN
    IF (
        ISINSCOPE ( 'Calendar'[Month-Day] )
            && _value <> BLANK (),
        IF (
            _value = _minValue,
            _minValue,
            _minValue & " * "
                & CONVERT ( DIVIDE ( _value, _minValue ), STRING )
        ),
        IF (
            ISINSCOPE ( 'Calendar'[QuarterName] ),
            BLANK (),
            SUMX ( 'Calendar', [Measure] )
        )
    )

The final output is shown below:

totals of per month.PNG

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-eqin-msft
Community Support
Community Support

Hi @John_Dozen34999 ,

 

Please follow these steps:

 

1. Add a Month-Day column:

Month-Day = FORMAT([Month Start],"MMM-DD")

2.Create a Calendar table:

Calendar =
CROSSJOIN (
    DISTINCT (
        SELECTCOLUMNS (
            CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) ),
            "Year", YEAR ( [Date] ),
            "QuarterName", "Q" & FORMAT ( [Date], "Q" ),
            "Month", MONTH ( [Date] ),
            "Month-Day", FORMAT ( [Date], "MMM-21" )
        )
    ),
    VALUES ( 'Table'[Employee] )
)

3. Add columns to Calendar table:

Amount Column = LOOKUPVALUE('Table'[Amount],[Employee],[Employee],'Table'[Month-Day],[Month-Day])
Frequency Column = LOOKUPVALUE('Table'[Frequency],[Employee],[Employee])

4. Now try the following formula to create measure:

Measure =
VAR _value =
    CALCULATE (
        SUM ( 'Calendar'[Amount Column] ),
        ALLEXCEPT ( 'Calendar', 'Calendar'[Employee] )
    )
VAR _minMonth =
    CALCULATE (
        MIN ( 'Calendar'[Month] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Frequency Column] = MAX ( 'Calendar'[Frequency Column] )
                && FIRSTNONBLANK ( 'Calendar'[Amount Column], TRUE () )
        )
    )
RETURN
    SWITCH (
        MAX ( 'Calendar'[Frequency Column] ),
        "Quarterly",
            IF (
                MAX ( 'Calendar'[Month] ) >= _minMonth
                    && MOD ( MAX ( 'Calendar'[Month] ) - _minMonth, 3 ) = 0,
                _value,
                BLANK ()
            ),
        "Annual", FIRSTNONBLANK ( 'Calendar'[Amount Column], TRUE () ),
        "Monthly", IF ( MAX ( 'Calendar'[Month] ) >= _minMonth, _value, BLANK () ),
        "BiAnnual",
            IF (
                MAX ( 'Calendar'[Month] ) = MAXX ( ALL ( 'Calendar' ), [Month] )
                    || MAX ( 'Calendar'[Month] ) = _minMonth,
                _value,
                BLANK ()
            )
    )

5. Use ISINSCOPE() to get cummulative total in Matrix:

Cummulative =
VAR _value =
    IF (
        [Measure] = BLANK (),
        BLANK (),
        SUMX (
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Employee] = MAX ( 'Calendar'[Employee] )
                    && 'Calendar'[Month] <= MAX ( 'Calendar'[Month] )
            ),
            [Measure]
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'Calendar'[Month-Day] ),
        _value,
        IF (
            ISINSCOPE ( 'Calendar'[QuarterName] ),
            BLANK (),
            SUMX ( 'Calendar', [Measure] )
        )
    )

The final output is shown below:

Cummulative per period frequency.PNG

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

thanks for your help. however im curious on the total in a monthly basis. It is not showing total per month

Hi @John_Dozen34999 ,

 

According to my understanding , you want to display

Employee1 ——100,  100*2 ,

Employee4 ——300,  300*2 , 300*3 ...

not just display the result

Employee1 ——100,200,   

Employee4 ——300 ,600 ,900 ... right?

Please modify the  Cummulative measure:

Cummulative =
VAR _value =
    IF (
        [Measure] = BLANK (),
        BLANK (),
        SUMX (
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Employee] = MAX ( 'Calendar'[Employee] )
                    && 'Calendar'[Month] <= MAX ( 'Calendar'[Month] )
            ),
            [Measure]
        )
    )
VAR _minValue =
    MINX (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Employee] = MAX ( 'Calendar'[Employee] )
        ),
        [Measure]
    )
RETURN
    IF (
        ISINSCOPE ( 'Calendar'[Month-Day] )
            && _value <> BLANK (),
        IF (
            _value = _minValue,
            _minValue,
            _minValue & " * "
                & CONVERT ( DIVIDE ( _value, _minValue ), STRING )
        ),
        IF (
            ISINSCOPE ( 'Calendar'[QuarterName] ),
            BLANK (),
            SUMX ( 'Calendar', [Measure] )
        )
    )

The final output is shown below:

totals of per month.PNG

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@John_Dozen34999 , Create a date table, with half year, month, qtr. and then create a measure like

 

sum(Table[Amount])+0 and then try to display this

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.