Weekly summary by current Week, MTD, QTD and YTD

[slavisha84]

Hello there, 

I have two tables, FiscalCalendar, and Revenue that use dates as cross-references.
The fiscal Calendar table contains: Date, FiscalWeek, FiscalMonth, MonthName, FIscalQuarter, and Fiscal Year columns. 
Revenue table contains: Date and Revenue columns.
What I am trying to do is to provide a summary on a weekly basis that will automatically update every week based on the fiscal week. The summary needs to have sum of revenue presented in  this format:

	ThisYear	LastYear	Variance	%YoY
CurrentWeek	$890	521	$369	70%
MTD	$2,903	$2,544	$359	14%
QTD	$8,986	$7,868	$1,118	14%
YTD	$36,556	$35,911	$645	2%

For this summary example, I used fiscal week 10 of 2021. So next week the summary should change to reflect the data as of fiscal week 11 of 2021. 

I have been stuck on this for the last 2 days and I have tried using Matrix for visuals, downloaded various different visualizations for variances but I think this needs to be done with DAX. 
I have years of Python experience but I am very new to DAX so I really need help with this one. 

I have uploaded sample.pbix file and excel data into my one drive so you can download it here:
https://1drv.ms/u/s!AhhZq1add5YwjYIvuASi76lCL3R1eA?e=sZQshq

Let me know if you can help with this.

[slavisha84]

Since I am not good with DAX yet and I don't understand your response above, I tried to write the summary table in python and upload it into PowerBI. I can see the table in power BI now which is exactly the same format with the same calculation as in the example in my first post. Is there a way I can now show this table as is next to my charts in the visual section? So the calculations are done in python and i don't need to change anything just to show these 5 columns with 5 rows as they are next to my other visuals. How do i do that?

Otherwise, if someone could help me write this in DAX I would so appreciate you and i don't mind even paying for it at this point because it is very urgent.

[amitchandak]

@slavisha84 , You have create a measure like this first

union(
summarize('Table',"Measure","MTD","This period",[SALES MTD],"Last period",[SALES LMTD],"POP",[SALES MOM])
summarize('Table',"Measure","YTD","This period",[unit YTD],"Last period",[unit LYTD],"POP",[unit YOY])
)

add any group by you want to use as filter as the filter will not work on this new  table

union(
summarize('Table','Table' [Category], "Measure","MTD","This period",[SALES MTD],"Last period",[SALES LMTD],"POP",[SALES MOM])
summarize('Table',Table' [Category], "Measure","YTD","This period",[unit YTD],"Last period",[unit LYTD],"POP",[unit YOY])
)

[slavisha84]

@amitchandak Thank you for your response Amit. 
I am having a hard time understanding the code in your solution. When I try to create a measure it creates it as a column in the exiting the table. What I need is to show that summary as the small table next to my other visuals.
Would you please show me how to do that with sampli.pbix I uploaded?