Splitting numbers between months given start and end dates

[cmath]

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:

• Splitting sales by month plotted on a graph
• Counting the number of unique IDs by month plotted on a graph

***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

[v-yiruan-msft · Accepted 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