Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
106 | |
97 | |
76 | |
63 | |
60 |