Calendar Table only in Months

[Anonymous]

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:

AccountingCALENDAR =

VAR BASECALENDAR =

CALENDAR ( DATE ( 2018, 8, 1 ), now()+365 )

RETURN

GENERATE (

BASECALENDAR,

VAR BASEDATE = [DATE]

VAR YEARDATE = YEAR ( BASEDATE )

VAR WEEKNO = if( WEEKNUM( basedate,2) <31, WEEKNUM( basedate,2)+21, weeknum (basedate,2) - 30)

var WEEKDY = WEEKDAY(basedate,2)

VAR MONTHNUMBER = format ( if(MONTH ( BASEDATE ) <8, MONTH ( BASEDATE ) +4, MONTH ( BASEDATE ) -7), "00")

VAR MONTHNAME = FORMAT ( BASEDATE, "MMMM" )

VAR QUARTER = if( or( month (BASEDATE) = 8, or(month (BASEDATE) = 9, month (BASEDATE) = 10)), "Q1", if(or( month (BASEDATE) = 11, or( month (BASEDATE) = 12, month (BASEDATE) = 1)), "Q2", if( or ( month (BASEDATE) = 2,or( month (BASEDATE) = 3, month (BASEDATE) = 4)), "Q3","Q4")))

VAR YEARMONTHNAME = FORMAT ( BASEDATE, "MMM YY" )

VAR FYEAR = if(MONTH ( BASEDATE ) <8,YEAR (BASEDATE)-1, YEAR(BASEDATE))

VAR ACCOUNTYEAR = IF (YEARDATE = FYEAR,right ( YEARDATE , 2) &"/"& right (YEARDATE+1, 2), right (YEARDATE-1,2) &"/"& right (YEARDATE ,2) )

RETURN ROW (

"F MONTH", MONTHNUMBER,

"F Qtr", FYEAR & QUARTER,

"MONTH", MONTHNAME,

"PERIOD", FYEAR & MONTHNUMBER,

"ACCOUNTING YEAR",ACCOUNTYEAR,

"YEAR MONTH", YEARMONTHNAME

)

)

However, if I do anything with the BaseCalendar after 'Generate' the whole thing throws errors.   Is there a simple fix to this that gives me single months rather than this:

Any help much appreciated. 

[v-juanli-msft · Accepted 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)])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Maggie,

You're a star!! Thanks for that.  I'd been trying to use a second table but was doing it all wrong.  

[amitchandak]

refer

https://community.powerbi.com/t5/Desktop/Calendar-w-months-interval/td-p/232049

[Anonymous]

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.  

[Anonymous]

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. 

[v-juanli-msft]

Hi @Anonymous 

The "Period", eg 201901, is it 2019/1 for the real date or for your financial year and month?

Best Regards
Maggie

[Anonymous]

Hi Maggie,

Financial Year and month, so 201901 would be August 2019.

[v-juanli-msft]

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")

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Oh, I had a search and saw 2 posts that looked like they could have helped but didn't solve this one...