Forum Discussion

Milozebre's avatar
Milozebre
Helper V
7 years ago
Solved

Calculate several date

Hello community,

I have a question about date calculation. I have a measure that calculates the number of tickets I have:

Nombre de tickets = CALCULATE(  DISTINCTCOUNT('dm_ticket_time d_ticket'[Ticket_id]))

I would like to create measures and make a table with the number of tickets for Current day, Yesteray, D-2, Current Week, W-1, W-2 and Current Month, M-1, M-2.

I see examples for the columns but to make the table I can't find anything or I don't look well

 

Today D-1D-2This weekW-1W-2This month M-1M-2
10598101704802652248522012365

 

Thnak you in advance for your help.

 

Dimitri

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Milozebre ,

     

    To achieve aggregation value in last week, you should add another "Week Number" column to flag the weeks in your original table. Then you can click "New Table" to create a calculate table using DAX like below:

    New Table =
VAR Today =
    TODAY ()
VAR D_1 = Today - 1
VAR D_2 = Today - 2
VAR This_Week =
    CALCULATE (
        MAX ( Table[Week Number] ),
        FILTER ( ALL ( Table ), Table[date] = Today )
    )
VAR W_1 = This_Week - 1
VAR W_2 = This_Week - 2
VAR This_Month =
    MONTH ( Today )
VAR M_1 = This_Month - 1
VAR M_2 = This_Month - 2
RETURN
    SUMMARIZE (
        Table,
        "Today", Today,
        "D-1", D_1,
        "D-2", D_2,
        "This Week", This_Week,
        "W-1", W_1,
        "W-2", W_2,
        "This Month", This_Month,
        "M-1", M_1,
        "M-2", M_2
    )

    Community Support Team _ Jimmy Tao

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

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    Milozebre ,

     

    You can create a calculate table using DAX functions like SUMMARIZE or SUMMARIZECOLUMNS like pattern below:

    Calculate Table =
SUMMARIZE (
    Table,
    "Current Day", DISTINCTCOUNT ( Table[Today] ),
    "W-1", DISTINCTCOUNT ( Table[W-1] )
)

    Community Support Team _ Jimmy Tao

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

    • Milozebre's avatar
      Milozebre
      Helper V
      7 years ago

      v-yuta-msft 

       

      Hello thank you for your answer. 

       

      I can't express myself and I am sorry.

      D-1 means date of the day -1 (day -1)

      D-2 signfie date of the day -2 (day -2)

      W-1 means the previous week

      W-2 means two previous weeks

      M-1 means the previous month

      M-2 means two previous months

      I would like to create 7 measures that would automatically give me the answer and that I could put in a table. Then I could apply a segment to designate the teams.

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        7 years ago

        Milozebre ,

         

        To achieve aggregation value in last week, you should add another "Week Number" column to flag the weeks in your original table. Then you can click "New Table" to create a calculate table using DAX like below:

        New Table =
VAR Today =
    TODAY ()
VAR D_1 = Today - 1
VAR D_2 = Today - 2
VAR This_Week =
    CALCULATE (
        MAX ( Table[Week Number] ),
        FILTER ( ALL ( Table ), Table[date] = Today )
    )
VAR W_1 = This_Week - 1
VAR W_2 = This_Week - 2
VAR This_Month =
    MONTH ( Today )
VAR M_1 = This_Month - 1
VAR M_2 = This_Month - 2
RETURN
    SUMMARIZE (
        Table,
        "Today", Today,
        "D-1", D_1,
        "D-2", D_2,
        "This Week", This_Week,
        "W-1", W_1,
        "W-2", W_2,
        "This Month", This_Month,
        "M-1", M_1,
        "M-2", M_2
    )

        Community Support Team _ Jimmy Tao

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