Iterate over dates

[Krcmajster]

I have a table that has multiple CCs, payment types and dates and I want to figure out how can I calculate how many days have passes between each payment type grouped by CC cost center. 

Example:

CC       PayType   Date       DaysPassed

CC1     Pay1         21/Jan     Blank

CC1     Pay1         24/Jan     3

CC1     Pay1         29/Jan     5

CC1     Pay1         30/Jan     1

CC1     Pay2         11/Jan     Blank

CC1     Pay2         15/Jan     4

CC2     Pay1         17/Jan     Blank

CC2     Pay1          23/Jan    6

CC2     Pay1          25/Jan    2

[v-xicai · Accepted solution]

Hi @Krcmajster ,

You may create measure like DAX below

DaysPassed = var _LastDate=CALCULATE(MAX('Table'[Date]),FILTER(ALLSELECTED('Table'), 'Table'[CC]=MAX('Table'[CC])&&'Table'[PayType]=MAX('Table'[PayType])&&'Table'[Date]<MAX('Table'[Date])))

return

DATEDIFF(_LastDate,MAX('Table'[Date]),DAY)

Choose table visual to display the result, set the [CC] as "Show items with no data".

Best Regards,

Amy 

Community Support Team _ Amy

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

[Mariusz]

Hi @Krcmajster 

Try this column

DaysPassed = 
VAR __date = 'Table'[Date]
VAR __previousDate =
    CALCULATE(
        MAX( 'Table'[Date] ),
        ALLEXCEPT( 'Table', 'Table'[CC], 'Table'[PayType] ),
        'Table'[Date] < __date
    ) 
RETURN 
IF( 
    NOT ISBLANK( __previousDate ),  
    __date - __previousDate 
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Krcmajster]

Not really working , it gives blank column