cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Krcmajster
Helper IV
Helper IV

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

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

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".

96.png

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.

 

View solution in original post

3 REPLIES 3
v-xicai
Community Support
Community Support

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".

96.png

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.

 

View solution in original post

Mariusz
Community Champion
Community Champion

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

 

Not really working , it gives blank column

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!