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
rpostelmans
Regular Visitor

Create dax formula for setting op aging group with different categorys to look historial duedate

I would like to create a calculated column or measure,with a dax formula

I would like to create a line graph that shows over time what the aging was.

I want to calculate these values:

1. Total of invoice amount that are NOT DUE, so Due date will be larger then the date that you are wathing at.

2. Total of invoice amount that are 1-30 days due, so the due date per invoice has to be 1 till 30 days smaller then the invoice that.

3. Same as above but then between 61-90 d

4. Every invoice which is due over more then 90 days.

I tried something like this:

Aging Group = SWITCH('Sales Customer Transactions'[DueDate].[Date] > TODAY(); "Not due";
                      DATEDIFF('Sales Customer Transactions'[DueDate].[Date];TODAY();DAY) >=1 && DATEDIFF('Sales Customer Transactions'[DueDate].[Date];TODAY();DAY) <=30;" 1-30 days ";
                      DATEDIFF('Sales Customer Transactions'[DueDate].[Date];TODAY();DAY) >=31 && DATEDIFF('Sales Customer Transactions'[DueDate].[Date];TODAY();DAY) <=60;" 31-60 days ";
                      DATEDIFF('Sales Customer Transactions'[DueDate].[Date];TODAY();DAY) >=61 && DATEDIFF('Sales Customer Transactions'[DueDate].[Date];TODAY();DAY) <=90; " 61-90 days ";
                      DATEDIFF('Sales Customer Transactions'[DueDate].[Date];TODAY();DAY) >90;" > 90days ")

But this code did not work. I get an error: in function switch comparing values true and false withy type text are not supported. 

Also if this did work. I would like to replace the Today() in every sentence with the date of the moment you are looking.

 

Example If i have an invoice that has an invoice date of 1-1-2019. This invoice has a due date of 30-1-2019 (January 30th 2019 (Dutch format)

If i use a linegraph and i look today then i see that the invoice belongs in the category  > 90 days, because the invoice is older then 90 days calculated from the duedate.

So if this invoice has not been payed yet, i do want to see it in the > 90 days category if i look at today. date

 

BUT i also want to look back in the line graph for example on 15 march 2019. 

On that date, this same invoice belongs to the aging category 31-60 days because the difference between the duedate:  january 30 2019 and the date i look at the line graph march 15th 2019 =  about 44 days 

 

I have no idea how i can accomplish this.

Is here some wizzard that can help me.

Thanks.

1 REPLY 1
Anonymous
Not applicable

Hi there. My advice to you:

 

Please break down the problem into smaller ones and post one at a time. It'll be easier for people to deal with this. Also, please make sure you're as clear about the problem as the day. Don't let people guess what you wanted. If you do, you'll be waiting for an answer longer.

 

Cheers.

 

Best

Darek

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.

Top Solution Authors