Calculate several date

[Milozebre]

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-1	D-2	This week	W-1	W-2	This month	M-1	M-2
105	98	101	704	802	652	2485	2201	2365

Thnak you in advance for your help.

Dimitri

[v-yuta-msft]

@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]

@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 · Accepted solution]

@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.