Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
Need some help on my visualisation. I have this data which is an Item List, with its invoice date then a "age" time between that invoice data and my input date (lets call it As Of Date) :
ITEM | INVOICEDATE | As of date | Invoice age |
P-100082 | 1/25/2016 0:00 | 1/4/2016 0:00 | 21 |
P-100083 | 1/25/2016 0:00 | 1/4/2016 0:00 | 21 |
P-100088 | 1/25/2016 0:00 | 1/4/2016 0:00 | 21 |
P-100249 | 2/8/2016 0:00 | 1/4/2016 0:00 | 35 |
P-100263 | 3/17/2016 0:00 | 1/4/2016 0:00 | 73 |
P-100264 | 3/17/2016 0:00 | 1/4/2016 0:00 | 73 |
P-100265 | 3/17/2016 0:00 | 1/4/2016 0:00 | 73 |
P-100266 | 3/17/2016 0:00 | 1/4/2016 0:00 | 73 |
P-100267 | 3/17/2016 0:00 | 1/4/2016 0:00 | 73 |
P-100359 | 3/14/2016 0:00 | 1/4/2016 0:00 | 140 |
P-100360 | 3/14/2016 0:00 | 1/4/2016 0:00 | 140 |
I also have a Range Table like this :
Name | From | To |
<=30 days | 0 | 30 |
31-90 days | 31 | 90 |
> 90 days | 91 | 9999 |
Then for the clasification of the age, I create a measure like below :
If I create Table Visualization, it looks correct :
ITEM | INVOICEDATE | As of date | Invoice age | Aging interval |
P-100082 | 1/25/2016 0:00 | 1/4/2016 0:00 | 21 | <= 30 days |
P-100083 | 1/25/2016 0:00 | 1/4/2016 0:00 | 21 | <= 30 days |
P-100088 | 1/25/2016 0:00 | 1/4/2016 0:00 | 21 | <= 30 days |
P-100249 | 2/8/2016 0:00 | 1/4/2016 0:00 | 35 | 31 - 90 days |
P-100263 | 3/17/2016 0:00 | 1/4/2016 0:00 | 73 | 31 - 90 days |
P-100264 | 3/17/2016 0:00 | 1/4/2016 0:00 | 73 | 31 - 90 days |
P-100265 | 3/17/2016 0:00 | 1/4/2016 0:00 | 73 | 31 - 90 days |
P-100266 | 3/17/2016 0:00 | 1/4/2016 0:00 | 73 | 31 - 90 days |
P-100267 | 3/17/2016 0:00 | 1/4/2016 0:00 | 73 | 31 - 90 days |
P-100359 | 3/14/2016 0:00 | 1/4/2016 0:00 | 140 | 91 - 150 days |
P-100360 | 3/14/2016 0:00 | 1/4/2016 0:00 | 140 | 91 - 150 days |
Question is why I cannot put the Aging interval Measure to COLUMNS by using Matrix Visualization ?
I want to create something like this :
ITEM | <= 30 days | 31 - 90 days | 91 - 150 days |
P-100082 | 1 | ||
P-100083 | 1 | ||
P-100088 | 1 | ||
P-100249 | 1 | ||
P-100263 | 1 | ||
P-100264 | 1 | ||
P-100265 | 1 | ||
P-100266 | 1 | ||
P-100267 | 1 | ||
P-100359 | 1 | ||
P-100360 | 1 |
Can someone help or advice what should I do ?
Thanks,
Solved! Go to Solution.
Hi,
The AND() function takes 2 inputs. Only when both inputs evaluate to TRUE will the AND() function also evaluate to TRUE. So if the Age of the invoice is >= min value and also <= max value, then it means that the Age of invoice falls between the min and ma values. The AND() function will therefore evaluate to TRUE. To convert TRUE to 1, one can multiply with 1.
Hope this clarifies.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi Ashish,
It works!!
I saw the measure, honestly I don't realy understand how it works ?
Can you be so kind to explain a bit what's the logic in there? Sorry, but I'm really a beginner in DAX. I did some DAX material, but that one, is fantastic yet not easy for me to know what's going on.
Thanks,
Hi,
The AND() function takes 2 inputs. Only when both inputs evaluate to TRUE will the AND() function also evaluate to TRUE. So if the Age of the invoice is >= min value and also <= max value, then it means that the Age of invoice falls between the min and ma values. The AND() function will therefore evaluate to TRUE. To convert TRUE to 1, one can multiply with 1.
Hope this clarifies.
Hi!
You can simplified this by creating a calculated column in your "items" table.
Create the calculated column with the next dax formula in your "items" table:
Hi,
Sorry, I forgot to tell, I also need the range to be from a table. And what I know is I need to use CALCULATE to do that, however there is another stopper, I cannot use CALCULATE since I'm using Direct Query.
So that is the background story why I create a measure. (CALCULATE is not allowed for CalculatedColumn)
Thanks,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |