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.
Hello @amitchandak ,
Looking for assistance with some calculations. I have a data table with some of the columns as below
ID | Start Date | End Date | Run Start Date | Run End Date | Expected Sales |
1 | 4/01/2023 | 20/04/2023 | 10 | ||
2 | 6/02/2023 | 15/05/2023 | 18/05/2023 | 25/05/2023 | 5 |
3 | 20/01/2023 | 4/03/2023 | 2 | ||
4 | 31/01/2023 | 1/02/2023 | 10/02/2023 | 25/02/2023 | 20 |
5 | 20/02/2023 | 25/02/2023 | 26/02/2023 | 5/05/2023 | 15 |
Output required:
***For Jan, the number of expected sales would be 13.16 and for Feb it will 29.4
***No. of unique runs in Jan will be 0 and for Feb will be 2
Solved! Go to Solution.
Hi @cmath ,
I created a sample pbix file(see the attachment), please find the details in it.
1. Create a date dimension table
2. Create a measure as below to get the count of IDs which fulfill the conditions
Count of IDs =
VAR _selYM =
VALUE ( SELECTEDVALUE ( 'Date'[YearMonth] ) )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
FILTER (
'Table',
NOT ( ISBLANK ( 'Table'[Run Start Date] ) )
&& VALUE (
YEAR ( 'Table'[Run Start Date] ) & FORMAT ( 'Table'[Run Start Date], "MM" )
) <= _selYM
&& IF (
ISBLANK ( 'Table'[Run End Date] ),
1 = 1,
VALUE (
YEAR ( 'Table'[Run End Date] ) & FORMAT ( 'Table'[Run End Date], "MM" )
)
) >= _selYM
)
)
3. Create a visual as below screenshot
And there is one doubt about the following requirement, what's the calculation logic of getting number of expected sales? Why is 13.16 for Jan and 29.4 for Feb? Could you please explain it with more details? Thank you.
***For Jan, the number of expected sales would be 13.16 and for Feb it will 29.4
Best Regards
Hi @cmath ,
I created a sample pbix file(see the attachment), please find the details in it.
1. Create a date dimension table
2. Create a measure as below to get the count of IDs which fulfill the conditions
Count of IDs =
VAR _selYM =
VALUE ( SELECTEDVALUE ( 'Date'[YearMonth] ) )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
FILTER (
'Table',
NOT ( ISBLANK ( 'Table'[Run Start Date] ) )
&& VALUE (
YEAR ( 'Table'[Run Start Date] ) & FORMAT ( 'Table'[Run Start Date], "MM" )
) <= _selYM
&& IF (
ISBLANK ( 'Table'[Run End Date] ),
1 = 1,
VALUE (
YEAR ( 'Table'[Run End Date] ) & FORMAT ( 'Table'[Run End Date], "MM" )
)
) >= _selYM
)
)
3. Create a visual as below screenshot
And there is one doubt about the following requirement, what's the calculation logic of getting number of expected sales? Why is 13.16 for Jan and 29.4 for Feb? Could you please explain it with more details? Thank you.
***For Jan, the number of expected sales would be 13.16 and for Feb it will 29.4
Best Regards
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |