Forum Discussion

Macwin's avatar
Macwin
Icon for Helper I rankHelper I
4 years ago
Solved

Calculate Amount with the same specific date range in every year

Hello, currently im trying to setup a measure for calculating a sales amount with a specific date range in every year.   For Example: Daterange -> 01.01 .. 10.31 Years -> 2017..2018..2019..2020 ...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Macwin 

    Power BI doesn't support you to show dates in dd/mm format in Between in slicer. Here I suggest you to create two unrelated dax table to create two slicer for MonthDay range.

    RangeStart = 
    VAR _LeapYear =
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2016, 01, 01 ), DATE ( 2018, 12, 31 ) ),
            "Month", MONTH ( [Date] ),
            "Day", DAY ( [Date] ),
            "Month/Day", FORMAT ( [Date], "mm/dd" ),
            "MonthDay",
                MONTH ( [Date] ) * 100
                    + DAY ( [Date] )
        )
    RETURN
        SUMMARIZE ( _LeapYear, [Month], [Day], [Month/Day], [MonthDay] )
    RangeEnd = 
    VAR _LeapYear =
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2016, 01, 01 ), DATE ( 2018, 12, 31 ) ),
            "Month", MONTH ( [Date] ),
            "Day", DAY ( [Date] ),
            "Month/Day", FORMAT ( [Date], "mm/dd" ),
            "MonthDay",
                MONTH ( [Date] ) * 100
                    + DAY ( [Date] )
        )
    RETURN
        SUMMARIZE ( _LeapYear, [Month], [Day], [Month/Day], [MonthDay] )

    Here I create a DimDate table has relationship with Data table.

    DimDate = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Quarter",QUARTER([Date]),"Month",MONTH([Date]),"Day",DAY([Date]),"MonthDay",MONTH([Date])*100+DAY([Date]))

    Measure:

    QTR BY MONTHDAY RANGE = 
    VAR _STARTMONTHDAY =
        SELECTEDVALUE ( RangeStart[MonthDay] )
    VAR _ENDMONTHDAY =
        SELECTEDVALUE ( RangeEnd[MonthDay] )
    RETURN
        CALCULATE (
            SUM ( 'Table'[Qtr] ),
            FILTER (
                DimDate,
                DimDate[MonthDay] >= _STARTMONTHDAY
                    && DimDate[MonthDay] <= _ENDMONTHDAY
            )
        )

    Result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.