Revenue YTD variance per category

[Anonymous]

Hi everyone,

I have a table with orderlines;

Orderline ID Order ID Date Cust. Nr. Value

1	1	1/1/2019	4	60
2	1	1/1/2019	4	36
3	2	2/1/2019	3	140

I also have a table with customer information;

Cust. nr. Cust. name Category

3	Potato farmer	Agriculture
4	Microsoft	Software

What I want is to calculate the variance in revenue between the different categories between current YTD vs last year YTD. I have the calculation for the total variance, however I can't split this up in categories.

How do I fix this? Do I need to change my DAX calculation?

Revenue YTD € = TOTALYTD(SUM('Ordertable'[Value]; 'Ordertable'[Date])

Revenue YTD € Last Year = CALCULATE([Revenue YTD €]; SAMEPERIODLASTYEAR('Ordertable'[Date]))

Variance Revenue % = [Revenue YTD €]/[Revenue YTD € Last Year]-1

[v-xicai · Accepted solution]

Hi @Anonymous ,

According to your description, I create sample data to test the scenario. You can create new calendar table Date, and then create relationship with the table Ordertable.

Date = CALENDARAUTO()

Then, you can create measures.

Revenue YTD € = TOTALYTD(SUM('Ordertable'[Value]),'Date'[DateKey])

Revenue YTD € Last Year = CALCULATE(SUM(Ordertable[Value]),SAMEPERIODLASTYEAR('Date'[DateKey]))

Choose table visual to display the result which show the variance in revenue between the different categories between current YTD vs last year YTD.

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ES__PrNFUQhLtaXZV7KIIG4BsV4yZaC2dHry2PLIEXZQHw?e=yOJf1q

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.