Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi friends,
i have a dataset with date, storekey, revenue, quantity, revenue_target, Quantity_target.
I need you help to create a new table inside PowerBI that contain all of measure including actual, MTD, YTD,Lastmonth values with date and storekey are dimension , i tried many times with few functions like sumarize() or addcolumn() but the values for MTD and YTD are always wrong or null.
Coud anyone help me or give your advices on this situation?
I could share my dataset as link:
https://www.dropbox.com/s/bt4c7z90zp4dys4/test.pbix?dl=0
Thank in advanced,
J.
Solved! Go to Solution.
Hi @michaelJack,
You can create a calculated table below:
Test_Sumarize = SUMMARIZE(
Revenue,
Revenue[StoreKey],
Revenue[OrderDate],
"Revenue_act",SUM(Revenue[Revenue]),
"Revenue_act_MTD",CALCULATE(SUM(Revenue[Revenue]),FILTER(ALL(Revenue),'Revenue'[OrderDate]<=MAX(Revenue[OrderDate]) && MONTH('Revenue'[OrderDate])=MONTH(MAX(Revenue[OrderDate])) && YEAR(Revenue[OrderDate])=YEAR(MAX(Revenue[OrderDate])))),
"Revenue_act_YTD",CALCULATE(SUM(Revenue[Revenue]),FILTER(ALL(Revenue),'Revenue'[OrderDate]<=MAX(Revenue[OrderDate]) && YEAR(Revenue[OrderDate])=YEAR(MAX(Revenue[OrderDate]))))
)
You can see attached pbix file.
Best Regards,
Qiuyun Yu
Hi @michaelJack,
You can create a calculated table below:
Test_Sumarize = SUMMARIZE(
Revenue,
Revenue[StoreKey],
Revenue[OrderDate],
"Revenue_act",SUM(Revenue[Revenue]),
"Revenue_act_MTD",CALCULATE(SUM(Revenue[Revenue]),FILTER(ALL(Revenue),'Revenue'[OrderDate]<=MAX(Revenue[OrderDate]) && MONTH('Revenue'[OrderDate])=MONTH(MAX(Revenue[OrderDate])) && YEAR(Revenue[OrderDate])=YEAR(MAX(Revenue[OrderDate])))),
"Revenue_act_YTD",CALCULATE(SUM(Revenue[Revenue]),FILTER(ALL(Revenue),'Revenue'[OrderDate]<=MAX(Revenue[OrderDate]) && YEAR(Revenue[OrderDate])=YEAR(MAX(Revenue[OrderDate]))))
)
You can see attached pbix file.
Best Regards,
Qiuyun Yu
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |