Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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