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.
Hey all,
its really hard to explain, so I'll try to show you what I want within the following table. What I want is a formula for the column "Number of Days".
As the table doesn't has values for every date, the formula for "Number of Days" has to refer to a calendar-table (I guess...), which is already created. Otherwise, you could say, that the date of the first apperance of a product could get the day number 1, but then I don't know how to do count the days that are missing in this table.
Vol | Date | Product | Number of Days |
1 | 24.07.2022 | A | 1 |
2 | 24.07.2022 | A | 1 |
3 | 25.07.2022 | A | 2 |
2 | 26.07.2022 | A | 3 |
2 | 28.07.2022 | A | 5 |
1 | 29.07.2022 | B | 1 |
I hope this is understandable.
Thank you very much in advance!
Solved! Go to Solution.
Hi, @phjz
You can't apply a measure to the x-axis of line chart.
You need to convert your original measure formula to calculated column formula.
Column_Number of days =
VAR basedate =
CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Product] ) )
RETURN
DATEDIFF ( basedate, Table1[Date], DAY ) + 1
Best Regards,
Community Support Team _ Eason
hello @phjz ,
please try :
Number of days =
VAR basedate = CALCULATE(MIN(Table1[Date]),ALLEXCEPT(Table1,Table1[Product]))
return
DATEDIFF(basedate,SELECTEDVALUE(Table1[Date]),DAY) +1
That does work really good - thank you so much!
There is one last problem with this: I want to compare Product A and B using a line diagram, but I can't put the measure on the x-axis. When I try to type the formula in the field for a new column, it doesn't work. Do you have an idea for this?
Hi, @phjz
You can't apply a measure to the x-axis of line chart.
You need to convert your original measure formula to calculated column formula.
Column_Number of days =
VAR basedate =
CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Product] ) )
RETURN
DATEDIFF ( basedate, Table1[Date], DAY ) + 1
Best Regards,
Community Support Team _ Eason
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |