Forum Discussion

Krcmajster's avatar
Krcmajster
Icon for Helper IV rankHelper IV
6 years ago
Solved

Iterate over dates

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

Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    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.

     

3 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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

     

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    6 years ago

    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.