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

Date comparison with a date estimated as the max of all dates in a database

Hello everyone,

 

I am currently working on a database of invoices which all have a date attribute "Invoice_Date". I defined the extraction date of my database as ExtractionDate = MAX ( [Invoice_Date] ), which is estimated properly (checked in the report mode).

 

I would like to cluster my invoices by age group (< 6 months, 6-12 months, > 12 months). However, when I define a new column with a condition such as : InvoiceAge = IF ( [Invoice_Date] > EDATE ([ExtractionDate];-6) ; "< 6 months" ; "> 6 months"), it always shows the same result in the whole database ("< 6 months").

 

I may have found the root cause of the problem but I cannot find a proper solution to it: when I hardcode ExtractionDate = DATE (2018 ; 04 ; 24), my new colum InvoiceAge works correctly. 

Therefore, the question is: how can I make my calculation InvoiceAge work with an automated estimation of ExtractionDate?

 

Many thanks in advance for your help.

 

Best regards,

Alpha 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@Alpha0689,

 

Make sure [ExtractionDate] is a calculated column, not measure. You may also replace [ExtractionDate] with MAX ( [Invoice_Date] ).

Community Support Team _ Sam Zha
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

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@Alpha0689,

 

Make sure [ExtractionDate] is a calculated column, not measure. You may also replace [ExtractionDate] with MAX ( [Invoice_Date] ).

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

@v-chuncz-msft

Thank you for the tips, both solutions work.

 

Regarding solution 1, I started using Power BI 2 days ago so I am quite a novice => In my view (but correct me if I am wrong), measures are useful not to waste a full calculated column with the same value. That's why, I persisted in finding a solution with a measure without understanding why it was not working.

 

However, I should have thought about solution 2 which is (in my view) cleaner and straightforward 🙂

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.