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,
We have OLAP cubes and I would like to create a date table that dynamically filters current and prior YTD dates only. I do not need to calculate or sum anything. Once I get my relevant dates filtering, I can create a hierarchy in power bi that will display my summed data for a profit and loss statement.
I'm thinking to create my table I need to incorporate a dateadd filter. The only dates I need in the date table would be date, month, month number, quarter and year.
Any help would be very appreciated.
Thank you.
Solved! Go to Solution.
hi,@CJH
After my research, you can do these follow my steps as below:
Step1:
Create a CALENDAR date first.
Then use this formula to create the date table that contains the current year and last year
Date = CALCULATETABLE('CALENDAR',YEAR('CALENDAR'[Date])<=YEAR(TODAY())&&YEAR('CALENDAR'[Date])>=YEAR(TODAY())-1)
Step 2
use these formulas to add column month, month number, quarter and year.
Year = YEAR('Date'[Date]) Quarter = INT ( FORMAT ( [Date], "q") ) Month number = MONTH ( 'Date'[Date] ) Month = FORMAT ( 'Date'[Date], "mmmm" )
Result:
by the way, when adding calculate table it can’t be affected by slicers.
And this link may give you some ideas.
Best Regards,
Lin
Thank you Lin!! That worked beautifully.
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |