Forum Discussion

cmath's avatar
cmath
Regular Visitor
3 years ago
Solved

Splitting numbers between months given start and end dates

Hello amitchandak ,

 

Looking for assistance with some calculations. I have a data table with some of the columns as below

 

IDStart DateEnd DateRun Start DateRun End DateExpected Sales
14/01/202320/04/2023  10
26/02/202315/05/202318/05/202325/05/20235
320/01/20234/03/2023  2
431/01/20231/02/202310/02/202325/02/202320
520/02/202325/02/202326/02/20235/05/202315

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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