Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Make sure [ExtractionDate] is a calculated column, not measure. You may also replace [ExtractionDate] with MAX ( [Invoice_Date] ).
Make sure [ExtractionDate] is a calculated column, not measure. You may also replace [ExtractionDate] with MAX ( [Invoice_Date] ).
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 🙂
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |