Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RH-BIP
Helper III
Helper III

Aging Group based on dateslicer

Hi!

 

New day, new challenge;)   @

 

My goal:

When i change the date slicer to a specific date, i need the data to show the amount outstanding, on that point, historical. I got that to work with these 3 measures;

 

1. selected date = MAXX(KEEPFILTERS(VALUES('Calendar'[Date])),CALCULATE(MAX('Calendar'[Date])))

2. invoicedate after selected date Y/N = IF([max invoice date] > [selected date],TRUE(),FALSE())
3. invoice amount outstanding = IF([invoicedate after selected date Y/N] = FALSE() , [Invoice_amount],0)
 
You can see the outcome in the attached printscreen below. The problem comes with the calculation of the aging group. It only works when i make a measure, see printscreen. I also tried it with a calculated column, but as you can see, the aging group is false. It shows "Due >365" on every row. I get that, because a calculated column only refreshes on reload, right? The aging groups based on the measure shows the right outcome, but only in the table. The requirement is to show it like the second printscreen. I think that only works when you use a column...how can i fix this? Does anyone have a clue?
 

Knipsel.PNGKnipsel2.PNG

1 ACCEPTED SOLUTION

Hi!

 

I found the solution I needed in this video --> https://www.youtube.com/watch?v=5_KDJGPjMnI

 

Thanks for al the help!

View solution in original post

12 REPLIES 12
v-lili6-msft
Community Support
Community Support

hi @RH-BIP 

This is a common Dynamic Segmentation problem, you could refer to these three blogs to get it:

https://www.daxpatterns.com/dynamic-segmentation/

https://powerbi.tips/2016/04/power-bi-histogram-with-bins/

https://powerpivotpro.com/2015/03/creating-a-histogram-with-a-user-defined-number-of-buckets/

 

Regards,

Lin

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

Hi!

 

I found the solution I needed in this video --> https://www.youtube.com/watch?v=5_KDJGPjMnI

 

Thanks for al the help!

RohiniP-26
Resolver I
Resolver I

hi @RH-BIP 

           you cannot create calculated column for this requirement. Measures will show only the summarized data unless we add rows to the table visual like (invoice date, due time, etc). I suggest you to create measures seperately for due< 30 days , due 90 -180, due 180-365, due >365. Then you can visualize like below

for ex:

 due time                       due<30    due 90-180     due 180-365    due >365

total amount

(on selected date)          351              256                 1246                   27

 

Hi! tried that also, but the amount is the same in every column. Because the aging days is zero, only when I add InvoiceDate the table expands and shows the correct aging days per row. Maybe you know why?

 

Due < 30 days = CALCULATE([SD invoice amount outstanding],FILTER(MeasureTable, [SD invoice Aging Days] >= 1 && [SD invoice Aging Days] < 30))
 
Due >365 days = CALCULATE([SD invoice amount outstanding],FILTER(MeasureTable,[SD invoice Aging Days] > 365))
 
Knipselsdf.PNG

@RH-BIP 

 

use DateDiff function to calculate difference of days between the minimum date and the selected date on slicer.

datediff = DATEDIFF(MINX('table1','table1'[Invoice Date]),CALCULATE(MAX('table1'[Invoice Date]),ALLSELECTED('Invoice Date')),DAY)+1
 
 calculate ageing in seperate measures.
<30 = IF([datediff]<=30,SUM('Table1'[SD Invoice Amount Outstanding]),0)\
30 to 365 = IF([datediff]>=31 && [datediff]<=365,SUM('Table1'[SD Invoice Amount Outstanding]),0)
>365  = IF([datediff]>365,SUM('Table1'[SD Invoice Amount Outstanding]),0)
 
finally sum all rows corresponding to ageing days
Due < 30 days = SUMX('Table1','Table1'[<30])
 Due 30 to 365 days = SUMX('Table1','Table1'[30 to 365])
Due > 365 days = SUMX('Table1','Table1'[>365])
 
try this, the above as measures
 
drag   Due < 30 days, Due 30 to 365 days, Due > 365 days   these meausres as columns to the table visual.

Thanks for helping, its driving me crazy for a while now 😉

 

I tried your approach, but does not work (yet). < 30 and Due < 30 days dont show anything, but as you can see there are plenty invoices within 30 days. 

 

*drag   Due < 30 days, Due 30 to 365 days, Due > 365 days   these meausres as columns to the table visual.* <--  I cant drag the measures as in the column field within a matrix. I only get these measures into a table. 

 

Hope you can help!

sdfa.PNG

@RH-BIP 

Share your dax that you have used on your dataset now 

 

DAX used:
datediff = DATEDIFF(MINX('FACT_rows','FACT_rows'[Invoice_date]),CALCULATE(MAX('FACT_rows'[Invoice_date]),ALLSELECTED(FACT_rows[Invoice_date])),DAY)+1
 
<30 = IF([datediff]<=30,SUM('FACT_rows'[Invoice_amount]),0)
>30 = IF([datediff]>30,SUM('FACT_rows'[Invoice_amount]),0)
 
Due <30 days = SUMX('MeasureTable','MeasureTable'[<30])
Due >30 days = SUMX('MeasureTable','MeasureTable'[>30])
 
this is the result:
 
sdf.PNG

@RH-BIP 

create a seperate date table

date = CALENDAR(MIN('FACT_rows'[Invoice_date]),TODAY())
 
dateDiff = DATEDIFF (MINX('FACT_rows','FACT_rows'[Invoice_date]),
CALCULATE ( MAX ( 'date table'[Date] ), ALLSELECTED ( 'date table') ),
DAY)
 
1.Give a one to many relationship between Date table and FACT_rows dataset using [Date] and [Invoice_date] column in respective tables.
2. Create measures in FACT_rows dataset as much as possible, better donot to create seperate measure table.
3. Remove +1 from datediff.
4. Use Date column in date table as slicer in the report
 
 
 
 
amitchandak
Super User
Super User
FarhanAhmed
Community Champion
Community Champion

Unfortunately you cannot create Dynamic Column Value because Computed Column is pre-calculated. You would require a Measure to make it dynamic

 

 







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




Yes, i know. I already made a measure, and it shows the correct data..but only in a table with the field (dimension) Invoice_date. When I remove Invoice_date all rows collapse to 1 row, also the aging groups. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.