Forum Discussion

lucifer's avatar
lucifer
New Member
3 years ago
Solved

Date Query

How to create measure of black Friday sale only each year(column format : Friday, November 27,2020)

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi lucifer ,

    I modify the samle data to multiple sales on same day.

    And modify the formula:

    Measure =
VAR _T1 =
    CALENDARAUTO ()
VAR _T2 =
    FILTER ( _T1, MONTH ( [Date] ) = 11 && WEEKDAY ( [Date], 2 ) = 4 )
VAR _T3 =
    FILTER (
        ADDCOLUMNS (
            _T2,
            "Rank",
                RANKX (
                    FILTER ( _T2, YEAR ( [Date] ) = YEAR ( EARLIER ( [Date] ) ) ),
                    [Date],
                    ,
                    ASC
                )
        ),
        [Rank] = 4
    )
VAR _Col =
    SELECTCOLUMNS ( _T3, "Blank Friday", [Date] )
RETURN
    SUMX ( FILTER ( 'Table', 'Table'[Date] - 1 IN _Col ), 'Table'[Sales] )

    Get the correct result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

4 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi lucifer ,

    According to your description, I create a sample.

    As you can see, Friday, November 27,2020 and Friday, November 26,2021 are blank fridays. Here's my solution.

    Create a measure.

    Measure =
VAR _T1 =
    CALENDARAUTO ()
VAR _T2 =
    FILTER ( _T1, MONTH ( [Date] ) = 11 && WEEKDAY ( [Date], 2 ) = 4 )
VAR _T3 =
    FILTER (
        ADDCOLUMNS (
            _T2,
            "Rank",
                RANKX (
                    FILTER ( _T2, YEAR ( [Date] ) = YEAR ( EARLIER ( [Date] ) ) ),
                    [Date],
                    ,
                    ASC
                )
        ),
        [Rank] = 4
    )
VAR _Col =
    SELECTCOLUMNS ( _T3, "Blank Friday", [Date] )
RETURN
    MAXX ( FILTER ( 'Table', 'Table'[Date] - 1 IN _Col ), 'Table'[Sales] )

    Get the result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

    • lucifer's avatar
      lucifer
      New Member
      3 years ago

      v-yanjiang-msft not working 
      i think this solution will not work for multiple sales on same day, your data has one sale on each day 

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Community Support
        3 years ago

        Hi lucifer ,

        I modify the samle data to multiple sales on same day.

        And modify the formula:

        Measure =
VAR _T1 =
    CALENDARAUTO ()
VAR _T2 =
    FILTER ( _T1, MONTH ( [Date] ) = 11 && WEEKDAY ( [Date], 2 ) = 4 )
VAR _T3 =
    FILTER (
        ADDCOLUMNS (
            _T2,
            "Rank",
                RANKX (
                    FILTER ( _T2, YEAR ( [Date] ) = YEAR ( EARLIER ( [Date] ) ) ),
                    [Date],
                    ,
                    ASC
                )
        ),
        [Rank] = 4
    )
VAR _Col =
    SELECTCOLUMNS ( _T3, "Blank Friday", [Date] )
RETURN
    SUMX ( FILTER ( 'Table', 'Table'[Date] - 1 IN _Col ), 'Table'[Sales] )

        Get the correct result.

        I attach my sample below for your reference.

         

        Best Regards,
        Community Support Team _ kalyj

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