Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm pretty new to building things using DAX, so am struggling with some programming syntax...
I've built a table to recognise months, quarters and years for our financial year. It would work perfectly except I can't use the Periods (which Accounting use as their unit for allocating transactions to time) as a Key field as it's not unique. I though the easy answer would be to simply create a table which runs months instead of a daily calendar, however I'm struggling to make that work.
Here's what works for the table:
Solved! Go to Solution.
Hi @Anonymous
To get distinct Period, create a new table
Table 2 = SUMMARIZE('Table','Table'[Period],'Table'[Financial Year],'Table'[Financial quarter],'Table'[Financial month],'Table'[Month Year(Actual)])
Hi @Anonymous
To get distinct Period, create a new table
Table 2 = SUMMARIZE('Table','Table'[Period],'Table'[Financial Year],'Table'[Financial quarter],'Table'[Financial month],'Table'[Month Year(Actual)])
Hi Maggie,
You're a star!! Thanks for that. I'd been trying to use a second table but was doing it all wrong.
refer
https://community.powerbi.com/t5/Desktop/Calendar-w-months-interval/td-p/232049
Thanks amiitchandek, I had read through that and couldn't get that to make the difference to my coding. I'm not sure whether I'm not doing it right or whether three's a different solution but that didn't give me the required end result.
After some effort and thought. I can get the linked example to work, however as soon as I try to shift to meet a financial year it all goes somewhat wrong. The columns I need are:
Period - This is linked to our financial year and in the format 201901 (year month) I need this to be unique values
Financial Quarter - Year and Quarter (19Q1)
Month - Name
Accounting Year - split years 18/19
Month Year - month followed by the calendar year (Aug 19)
Our financial year is 1 August to 31 July and I have successfully built a table that built that but I can't link by the right factor. Unfortunately our Finance Department use Period to determine where costs are placed and the transaction dates can be misleading. I am never going to get that changing so much report differently.
Hi @Anonymous
The "Period", eg 201901, is it 2019/1 for the real date or for your financial year and month?
Best Regards
Maggie
Hi Maggie,
Financial Year and month, so 201901 would be August 2019.
Hi @Anonymous
Create a calendar table
Table = CALENDAR(DATE(2017,8,1),DATE(2020,7,31))
Create calculated columns
Financial Year = IF(MONTH([actual Date])>=8,YEAR([actual Date]),YEAR([actual Date])-1) newmonth order = IF(MONTH([actual Date])<=7,MONTH([actual Date])+12,MONTH([actual Date])) Financial month = RANKX(FILTER(ALL('Table'),'Table'[Financial Year]=EARLIER('Table'[Financial Year])),[newmonth order],,ASC,Dense) Period = var t=IF(LEN([Financial month])=1,"0"&[Financial month],[Financial month]) return [Financial Year]&t Financial quarter = var quarter1=IF([Financial month]<=3,"Q1",IF([Financial month]<=6,"Q2",IF([Financial month]<=9,"Q3","Q4"))) RETURN [Financial Year]&quarter1 Month Year(Actual) = FORMAT([actual Date],"Mmm YY")
Oh, I had a search and saw 2 posts that looked like they could have helped but didn't solve this one...
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |