Help

[Anonymous]

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 will show sales by FQ. When select FM, the line chart will show sales by FM, and so on. The solution in Tableau, I just need to create a Time parameter(FQ,FW,EW), and also a calculated field to pass values to the time parameter. How can I achive this in PowerBI?

        Thanks for help!

Jenny

[Anonymous]

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 · Accepted solution]

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]

Hi Yu,

         Very helpful, appreicate your help!!!

Jenny 

[Anonymous]

Hi Yu,

        Thank you very much for reply! When I plug in Survey Submit Day to axis, why 'week' doesn't show up. please see attched picture. I need week data, how can I let 'week' show up at axis. 

        Thanks!

[v-qiuyu-msft]

Hi @Anonymous, 

In Power BI, there is no exact very same feature. But there is a drill down feature for us to display sales in a chart on different level (quarter, month and week). You can create three calculated columns to return quarter, month and week values. The put these three columns in Axis bucket as well. For detail information, you can see the sample report. 

Reference: 

https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill

Best Regards,
Qiuyun Yu