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'[...
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.

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.