Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calendar Table only in Months

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. 
 
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

9 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Maggie,

       

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.