Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello community,
I have a question about date calculation. I have a measure that calculates the number of tickets I have:
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
Solved! Go to Solution.
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.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |