Forum Discussion

odeta's avatar
odeta
Regular Visitor
8 years ago
Solved

Dinamical Column Sum According to Selected Date Filter

Hello, I have data like this (data from 2016-06-30 until 2016-09-06 with all dates):     Sample data can be found here: https://1drv.ms/f/s!AiAA2np9BkqaaAg7NacBvej_2F0.   Below it is filtered...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi odeta,

     

    Please check out the demo here.

    1. Add a calendar table without relationship to other tables.

    2. Create several measures.

    No Delay =
SUMX (
    FILTER (
        SUMMARIZE (
            'data',
            data[Customer],
            data[DueDate],
            "Amount", SUM ( data[Amount] ),
            "Delay", MAX ( 'Calendar'[Date] ) - [DueDate]
        ),
        [Delay] < 0
    ),
    [Amount]
)

    and other four measures in the demo.

    Dinamical_Column_Sum_According_to_Selected_Date_Filter

     

    Best Regards,

    Dale

     

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi odeta,

 

Please check out the demo here.

1. Add a calendar table without relationship to other tables.

2. Create several measures.

No Delay =
SUMX (
    FILTER (
        SUMMARIZE (
            'data',
            data[Customer],
            data[DueDate],
            "Amount", SUM ( data[Amount] ),
            "Delay", MAX ( 'Calendar'[Date] ) - [DueDate]
        ),
        [Delay] < 0
    ),
    [Amount]
)

and other four measures in the demo.

Dinamical_Column_Sum_According_to_Selected_Date_Filter

 

Best Regards,

Dale