Need help to create a table containing actual, MTD,YTD,Lastmonth

[michaelJack]

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.

[v-qiuyu-msft · Accepted 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