Cumulative total % with total in Dax

[Anonymous]

I am working on power bi requirment where I have the Date of service, Date of Entry ,Collection and Allowed amount.The data look like below

Date of Service	Date of Entry	Collection	Allowed
01 June 2021	02 June 2021	1000	1100
01 July 2021	01 July 2021	2000	2500
01 August 2021	01 August 2021	2000	2400
01 September 2021	01 September 2021	1800	2300
01 October 2021	04 October 2021	2200	2100

I have put the above data in the pivot and looks like below with Date of service in row and Date of Entry in the column and Collection in value. .Looks  like below 

Now My requirement is finding the cumulative on  the Date of entry  and finding the % on allowed amount :I need below metrics in Power bI.The calculation for % is given in the rescpective cells

Below given the expected metircs where I have given the formula which should be done with help Dax in powe BI

Please help to find this Dax or some work around to achieave this 

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
CROSSJOIN(
    DISTINCT('Table'[Date of Service]),DISTINCT('Table'[Date of Entry]))

2. Create calculated column.

Flag =
var _month1=MONTH('Table 2'[Date of Service])
var _month2=MONTH('Table 2'[Date of Entry])
return
IF(
    _month1 = _month2 ,0, _month2  - _month1)

Flag1 =
SUMX(FILTER(ALL('Table'),
   MONTH('Table'[Date of Service])= MONTH(EARLIER('Table 2'[Date of Service]))&&
    AND(
    MONTH('Table'[Date of Entry])>=(MONTH(EARLIER('Table 2'[Date of Entry]))-[Flag]),
    MONTH('Table'[Date of Entry])<=MONTH(EARLIER('Table 2'[Date of Entry])))),[Allowed])

Value1 =
VAR _month1 =
    MONTH ( 'Table 2'[Date of Service] )
VAR _month2 =
    MONTH ( 'Table 2'[Date of Entry] )
VAR _sum1 =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            MONTH ( 'Table 2'[Date of Service] ) = MONTH ( 'Table'[Date of Service] )
                && MONTH ( 'Table 2'[Date of Entry] ) = MONTH ( 'Table'[Date of Entry] )
        ),
        [Collection]
    )
VAR _sum2 =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            MONTH ( 'Table 2'[Date of Service] )
                IN SELECTCOLUMNS ( 'Table', "1", MONTH ( 'Table'[Date of Service] ) )
                    && MONTH ( 'Table'[Date of Service] ) = MONTH ( 'Table 2'[Date of Service] )
        ),
        [Allowed]
    )
VAR _sum3 =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            MONTH ( 'Table'[Date of Service] )
                = MONTH ( EARLIER ( 'Table 2'[Date of Service] ) )
                && AND (
                    MONTH ( 'Table'[Date of Entry] )
                        >= ( MONTH ( EARLIER ( 'Table 2'[Date of Entry] ) ) - [Flag] ),
                    MONTH ( 'Table'[Date of Entry] )
                        <= MONTH ( EARLIER ( 'Table 2'[Date of Entry] ) )
                )
        ),
        [Allowed]
    )
RETURN
    IF (
        [Flag1] = BLANK (),
        BLANK (),
        IF ( _month1 = _month2, DIVIDE ( _sum1, _sum2 ), DIVIDE ( _sum3, _sum2 ) )
    )

3. Result:

Best Regards,

Liu Yang

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

[PaulDBrown]

Sorry, I'm not following the calculation. There is only one value for each date of service and date of entry, so the cumulative & division will return the same value moving forward. Is that correct?