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.
hi
i was looking for dax to create a period table
from where i can pick a period like rolling 6 months, last quarter etc...
i have listed time period l am looking to create
Solved! Go to Solution.
Hi @Anonymous ,
Indeed, it will be a little troublesome to use dax.
1.Create a calendar table and a category table.
Table = CALENDAR(DATE(2020,1,1),DATE(2021,12,31))
2.Create a relationship between calendar table and main table.
3.Create the measure
FilteredValue =
SWITCH (
SELECTEDVALUE ( 'Table (2)'[Category] ),
"Today", CALCULATE ( SUM ( MainTable[Value] ), FILTER ( 'Table', [Date] = TODAY () ) ),
"Yesterday",
CALCULATE (
SUM ( MainTable[Value] ),
FILTER ( 'Table', [Date] = TODAY () - 1 )
),
"Month to Date",
CALCULATE (
SUM ( MainTable[Value] ),
DATESINPERIOD ( 'Table'[Date], TODAY (), -1, MONTH )
),
"Rolling 7 days",
CALCULATE (
SUM ( MainTable[Value] ),
FILTER ( 'Table', [Date] <= TODAY () && [Date] >= TODAY () - 6 )
)
)
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Indeed, it will be a little troublesome to use dax.
1.Create a calendar table and a category table.
Table = CALENDAR(DATE(2020,1,1),DATE(2021,12,31))
2.Create a relationship between calendar table and main table.
3.Create the measure
FilteredValue =
SWITCH (
SELECTEDVALUE ( 'Table (2)'[Category] ),
"Today", CALCULATE ( SUM ( MainTable[Value] ), FILTER ( 'Table', [Date] = TODAY () ) ),
"Yesterday",
CALCULATE (
SUM ( MainTable[Value] ),
FILTER ( 'Table', [Date] = TODAY () - 1 )
),
"Month to Date",
CALCULATE (
SUM ( MainTable[Value] ),
DATESINPERIOD ( 'Table'[Date], TODAY (), -1, MONTH )
),
"Rolling 7 days",
CALCULATE (
SUM ( MainTable[Value] ),
FILTER ( 'Table', [Date] <= TODAY () && [Date] >= TODAY () - 6 )
)
)
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It is better to create in Excel or SQL.
Regards,
Manikumar
Proud to be a Super User!
@Anonymous
Probably simpler to create the table in Excel and import it, no?
Proud to be a Super User!
Paul on Linkedin.
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |