cancel
Showing results for 
Search instead for 
Did you mean: 
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
Eyelyn9
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.

View solution in original post

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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!