Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 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
7 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
7 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. 

 

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

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

      Hi Maggie,

       

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

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

        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.