Data difference between two dates

[Anonymous]

Hello, I am a total newbie into PoweBi and I need to get the difference between two months of fruit eaten, to see how much more or less fruit did I eat this month comapred to the last one. Since my english is poor, let me show you, what I mean on example:

 this is how the data looks like:

date	fruit	eaten
3-22	Apples	5
3-22	Oranges	2
4-22	Apples	4
4-22	Oranges	3

this is what I need:

fruit	eaten 3-22	eaten 4-22	difference
Apples	5	2	-3
Oranges	4	3	-1

the calculation should be: [eaten 4-22]-[eaten 3-22]=difference

How can I make this in powerBi? Is it possible to change column values by filter? Like if I wanted to change 3-22 to 1-22 or any other month? If I could chose the month values in slicer, that would be awesome. 

Thanks to anyone willing ot help. 🙂 

[Anonymous]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

eaten 3-22 =
IF(
    MAX('Table'[fruit])="Apples",
CALCULATE(SUM('Table'[eaten]),FILTER(ALL('Table'),'Table'[date]=DATE(2022,3,22)&&'Table'[fruit]="Apples")),
CALCULATE(SUM('Table'[eaten]),FILTER(ALL('Table'),'Table'[date]=DATE(2022,4,22)&&'Table'[fruit]="Apples")))

eaten 4-22 =
IF(
    MAX('Table'[fruit])="Apples",
CALCULATE(SUM('Table'[eaten]),FILTER(ALL('Table'),'Table'[date]=DATE(2022,3,22)&&'Table'[fruit]="Oranges")),
CALCULATE(SUM('Table'[eaten]),FILTER(ALL('Table'),'Table'[date]=DATE(2022,4,22)&&'Table'[fruit]="Oranges")))

difference = [eaten 4-22] - [eaten 3-22]

2. Result:

Please click here for the pbix file

Best Regards,

Liu Yang

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

[Anonymous]

Is there a solution without using the name of fruit? Let's say I have hunderts of different fruit names, so I would need this to be variable. Like differ every value from column "fruit". 

[davehus]

Hi @Anonymous ,

Have you got a date table in your model?