Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Help

Hi,          I have a line chart. X axis is Order Date, Y axis is sales. I want to set a prameter which includes Fiscal Quaterly, Fiscal Monthly and Fiscal Weekly. When I selcet FQ, my line chart wi...
Dashboards
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    7 years ago

    Hi Anonymous, 

     

    The auto date hierarchy doesn't contain week hierarchy, we need to create a calculated column use WeekNUM() function to get week number. 

     

    Assume fiscal year starts from February 1st, you can create columns below: 

     

    Fiscal Year = IF(MONTH('Table1'[Order Date]) <=2,YEAR('Table1'[Order Date]),YEAR('Table1'[Order Date]) +1)

     

    Fiscal Month = IF(MONTH('Table1'[Order Date])>=2,MONTH('Table1'[Order Date])-1,12)

     

    Fiscal Week = VAR fw = [Week] - 5 + 1
    RETURN IF( fw <= 0 , 52 + fw ,fw )

     

     

    Best Regards,
    Qiuyun Yu 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Yu, 

         based on the sample report that you showed, how can I calculate fiscal Month, fiscal week etc for orderdate. Assum that fisal year start t Feb 1 of year. 

        Thanks!

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
7 years ago

Hi Anonymous, 

 

The auto date hierarchy doesn't contain week hierarchy, we need to create a calculated column use WeekNUM() function to get week number. 

 

Assume fiscal year starts from February 1st, you can create columns below: 

 

Fiscal Year = IF(MONTH('Table1'[Order Date]) <=2,YEAR('Table1'[Order Date]),YEAR('Table1'[Order Date]) +1)

 

Fiscal Month = IF(MONTH('Table1'[Order Date])>=2,MONTH('Table1'[Order Date])-1,12)

 

Fiscal Week = VAR fw = [Week] - 5 + 1
RETURN IF( fw <= 0 , 52 + fw ,fw )

 

 

Best Regards,
Qiuyun Yu 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Yu,

             Very helpful, appreicate your help!!!

    Jenny