Dec 01 2020 03:35 AM
Hi guys
I am using power query.
the original data is aggregated, so to find values on a certain day, i have just asked the pivot table to show difference from previous item. Read below
I have created a pivot table (image 1)
that pivot table displays email sends per campaign id
the values in the pivot table are based as 'difference from' date and 'base item' previous (image 2)
When i insert a slicer, the dates disappear because its looking at the previous item, which has been removed by the slicer (image 3)
How do i go about solving this issue please :)
Dec 01 2020 11:14 AM
I'd suggest creating the PivotTable add data to data model and using Power Pivot DAX create measures which will calculate the difference of values with ones for basis date of each ID.
Dec 01 2020 12:32 PM
@Sergei Baklan Sounds like a plan! How do I go about creating the measure?
Im using excel 2013, so i am using calculated fields.
What will be the correct formula?
Thanks
Dec 02 2020 07:43 AM
If I understood the logic correctly we'd like to pivot like this
If create the measure
Value to Prev:=
VAR currentDate=MAX(Table1[Date])
VAR minDate=CALCULATE(
MIN(Table1[Date]),
ALL(Table1[Date])
)
VAR prevDate=CALCULATE(
MAX(Table1[Date]),
ALL(Table1[Date]),
KEEPFILTERS(Table1[Date] < currentDate)
)
VAR prevValue=CALCULATE(
MAX(Table1[Value]),
Table1[Date]=prevDate
)
VAR currentValue=MAX(Table1[Value])
VAR Result = IF(
currentDate=minDate,
BLANK(),
currentValue - prevValue
)
RETURN Result
filtered values will be like
Dec 02 2020 08:10 AM - edited Dec 02 2020 11:24 AM
@Sergei Baklan Hello Thank you for your reply,
Actually, your example above for the 2020-01-25 slicer should actually be 8 not 3, because that's the difference to date 2020-01-15. The middle table equation you did is three which is the difference. I don't think that's correct.
Also, 2020-01-25 should be 1006.
Somehow, it needs to memorise the figure and display after slicer has been implemented.
Also, your sheet doesn't work. I am using excel 2013 on professional plus...you able to convert or can you save the formula on my files?
I have added my workbook here for you to have a look at please?
Dec 02 2020 12:21 PM
Sorry, but I did as in your model. If the take source data for ID=100, they are
and in your PivotTable it is shown as
2230-2196 = 34
2196-2191 = 5
Based on your comments it shall show 2230 for Nov 30, and after that difference. If you'd like to change current logic as in your file, please explain which on it shall be.
2013 and 365 files are in same format, which error do you have opening the file?
Dec 03 2020 01:27 AM
So sorry Sergei,
I was wrong about the calculation. Indeed, your pivot table displays the correct number. Apologies again.
Just to reiterate, for ID 101. The difference between 15/01/2020 - 25-01-2020 is 3 of which your centre table and pivot slicer presents which is correct and what I'm looking for.
Ok, now that's clear. The images attached are the errors when i try to open from the ribbon powerpivot > manage the second image is when i try to click the slicer.
Dec 03 2020 03:49 AM
I see, thank you. It looks like Power Pivot add-in for 2013 is very outdated, even if data models are compatible, DAX operators - not. There is no way to convert DAX syntax back, and I don't remember how to re-write DAX formula in compatible with 2013 way.
Will try to play with it, but no promises.
Dec 03 2020 04:09 AM - edited Dec 03 2020 04:33 AM
No, thank you, Sergei. I've been stuck on this for many months and you're the only one who helped.
Do you know how i upgrade to 2013 power pivot?
Ive added you via linked in...
Dec 03 2020 04:51 AM
Based on this link Download Microsoft PowerPivot for Excel 2010 and PowerPivot in Excel 2013 Samples from Official Micr... Power Pivot for 2013 was last time updated almost 8 years ago.
You may try to download it from here. Not sure it will help, but who knows. At least you will have the latest Power Pivot for 2013.
Dec 03 2020 04:57 AM
Dec 03 2020 06:03 AM
SolutionI added calculated column
with formula
=IF(
ISBLANK(
CALCULATE(
MAX(Table1[Value]),
FILTER(ALLEXCEPT(Table1,Table1[ID]),Table1[Date]<EARLIER(Table1[Date])))
),
BLANK(),
Table1[Value]-CALCULATE(
MAX(Table1[Value]),
FILTER(ALLEXCEPT(Table1,Table1[ID]),Table1[Date]<EARLIER(Table1[Date]))
)
)
and add it into PivotTable
Please check if you may open the model in attached file.
Dec 04 2020 03:11 AM
@Sergei Baklan Hey, sorry for delay in responding, been working on this.
Ok, the work book works :D AND i have attempted to insert the formula. image 1 will show this.
Image 2 shows a query error? Im not sure what this is?
Could you help? Im nearly there Sergei i believe, i really appreciate your help.
thanks
Dec 04 2020 03:38 AM
@Sergei Baklan Sergei! It worked! haha! I just rewrote it using the 'tab' on keyboard.
It works! I cant believe it!
GENIUS!
Please accept my invite on linkedin
Dec 04 2020 08:39 AM
Great to know, thank you for sharing. I'm quite seldom on LinkedIn, will do when be next time.
Dec 03 2020 06:03 AM
SolutionI added calculated column
with formula
=IF(
ISBLANK(
CALCULATE(
MAX(Table1[Value]),
FILTER(ALLEXCEPT(Table1,Table1[ID]),Table1[Date]<EARLIER(Table1[Date])))
),
BLANK(),
Table1[Value]-CALCULATE(
MAX(Table1[Value]),
FILTER(ALLEXCEPT(Table1,Table1[ID]),Table1[Date]<EARLIER(Table1[Date]))
)
)
and add it into PivotTable
Please check if you may open the model in attached file.