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.
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.