Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a data set, which includes a column for year (2018 and 2019) and a column for calendar week (1,2,3...)
Is it possible to format the columns so that they are recognized as year and calendar week ?
The data set is too large for a daily basis data set.
Thanks
Anil
Solved! Go to Solution.
Hi,
have you a Year dimension table? Or have you just one large table?
If you have a table Calendar with the list of Year, you can create these measures:
TotalValue = SUM(Value)
TotalValue PY = CALCULATE([TotalValue], FILTER( ALL(Calendar), Calendar[Year] = MAX(Calendar[Year])-1))
Total Value YoY = TotalValue - TotalValue PY
% vs TotalVaue PY = DIVIDE(Total Value YoY, TotalValue PY)
Try this -
I believe my problem description was not well described from my side.
When I build graphs within Powerbi, I want to calculate the sales change in % between 2019 and 2018.
Under quick measures I can select the calculation " change in comparison to previous year". Aftwerwards, I need to select the data field, based on which the previous year calculation should be made.
The problem starts after this point. I cannot select the column "Year" as a basis for the previous year calculation. It is not accepted as a date format. Since I have only the year and not the day, I could not find a way to solve this issue.
Thanks for your time
Hi,
have you a Year dimension table? Or have you just one large table?
If you have a table Calendar with the list of Year, you can create these measures:
TotalValue = SUM(Value)
TotalValue PY = CALCULATE([TotalValue], FILTER( ALL(Calendar), Calendar[Year] = MAX(Calendar[Year])-1))
Total Value YoY = TotalValue - TotalValue PY
% vs TotalVaue PY = DIVIDE(Total Value YoY, TotalValue PY)
So you want to calculate Year-on -Year % change?
Yes, but my column "year" is not recognized in the selection field as a valid option.
Even though I tricked and entered fake dates I recieved the following error message (sorry for german)
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |