Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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!
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
Hi Yu,
Very helpful, appreicate your help!!!
Jenny
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |