• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Pivot with expiry and not expiry date

Kamo

New Member
Hi,

I try to get a Pivot Table in Power Pivot according to the result given in the attached file
Can you please help me?
All solutions are welcome : Power query, Pivot Table or DAX

Thank you in advance

Crossposting here
 

Attachments

Chihiro

Excel Ninja
Not enough info to help you here.

What constitutes Failed / Not Failed condition? What logical criteria is used?

Remember, that we are not mind readers, and you need to assume we don't know anything about your process/logic.
 

Kamo

New Member
Hi Chihiro,

Thank you for taking an interest in my problem,
I'm sorry, I did go too fast without the explanations,

I would like to have in a new column, the amounts whose date in column " Fallen On" is higher than the Export to column, (here the 07/31/2020)

You will find more explanations in the Excel file

Thank you in advance
 

Attachments

Last edited:

pecoflyer

Active Member
Cross post without links
 

vletm

Excel Ninja
Kamo
You should reread Forum Rules
Those are for You too.
 

Kamo

New Member
Hi pecoflyer, vletm,

Concerning cross posting, I had just informed by a link in my first post on this forum,
On the other hand, I couldn't modify any more the thread on the Excel guru forum and I couldn't report it anymore

Otherwise, would anyone have a solution to my problem?
I'm sorry, I don't speak English very well...

Thank you for your understanding

Have a nice day.
 

vletm

Excel Ninja
Kamo
I understand that 'measure twice - cut once'.
As well as written in Forum Rules:
Please take a few minutes to read this list of Rules, Procedures and Etiquette's for posting here at the Chandoo.org Forums.
After You, as well as everybody too, have read those before continue eg posting, You would know some basic rules.
If someone try to do shortcuts ... there would be delays.
eg with cross-posting, there are clear steps - how to do with those?

>>> Kamo <<<
I try to
help You to get replies,
I would be more possible, if You too could follow Forum Rules.
 
Last edited:

Kamo

New Member
vletm,

I've read the rules and I know what cross-posting is,
I'm sorry, but I don't speak English very well,
If there's no one to solve my problem then too bad for me

Have a nice day.
 

Chihiro

Excel Ninja
@Kamo

Patience, we are just bunch of people that help on our free time. I'm busy this weekend, but will revisit your post on Monday.
 

Kamo

New Member
Chihiro,

Thank you very much, dear, I'd really appreciate it :) .
I confess that I would like if possible have a preference for a solution with Power Pivot than Power query.

Have a nice day.
 

GraH - Guido

Well-Known Member
I was asking myself the same question when I was on your thread, I honestly do not have a clue.
There is for sure a way to get rid of these zero numbers and have blanks, but I doubt you can write a measure that won't show up as a column even if all is blank. @Chihiro might know, since he is our grand master when it comes down to DAX. For sure he will come back on this as he promised in #9.
 

Kamo

New Member
Thanks, GraH - Guido, no problem :),
It seems to me that it's possible with DAX or maybe with a mix of power query and Dax !
I'm curious to see the solution

Have a nice day.
 

GraH - Guido

Well-Known Member
Hi again,

Did not think of the obvious solution as I was concentrated on the DAX. You can filter them out of the pivot :rolleyes:. See attached.
Started something with PQ, but since it is using the same trick in the pivot, you may dismiss that all together.
 

Attachments

Kamo

New Member
Grah - Guido,

Thank you for this alternative,
Unnecessary columns are unfortunately still present but without the zero values,
The ideal is to have only the columns with the values
 

GraH - Guido

Well-Known Member
Indeed Kamo, I missed the detail the filter excluded also the other years.
Did continue on with PQ. It's the result you are after, but I do not like what I did, as in a certain step column headers need to be renamed and that step is not dynamic at all.

70724

Now that I'm typing this and looking at the picture, I may have another solution on my sleeve.
 

Attachments

GraH - Guido

Well-Known Member
Load table1 (fact) as connection only
Perform the code below

Load table2 (customers) as merge with table 1, and expand.
Save and load to Excel as Table.

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", Int64.Type}, {"Posting Date", type date}, {"Export to", type date}, {"Invoice Date", type date}, {"Fallen On", type date}, {"Amount", Int64.Type}}),
    CalcFailed = Table.AddColumn(#"Changed Type", "Failed", each if [Fallen On]<=[Export to] then "Failed" else "Not Failed"),
    InsertYear = Table.AddColumn(CalcFailed, "Year", each Date.Year([Fallen On]), Int64.Type),
    SortOnYear = Table.Sort(InsertYear,{{"Year", Order.Ascending}}),
    AddIndexToKeepSort = Table.AddIndexColumn(SortOnYear, "Index", 0, 1),
    RemoveCols = Table.RemoveColumns(AddIndexToKeepSort,{"Index"}),
    GroupOnYearIdFailed = Table.Group(RemoveCols, {"Year", "Customer ID", "Failed"}, {{"Amount", each List.Sum([Amount]), type number}}),
    MergeFailedYear = Table.CombineColumns(Table.TransformColumnTypes(GroupOnYearIdFailed, {{"Year", type text}}, "nl-BE"),{"Failed", "Year"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Failed Year"),
    PivotFailedYear = Table.Pivot(MergeFailedYear, List.Distinct(MergeFailedYear[#"Failed Year"]), "Failed Year", "Amount", List.Sum)
in
    PivotFailedYear
 

Attachments

Kamo

New Member
@GraH - Guido ,

Sorry for the late answer, I came home later than I expected.
Thank you for the latest Power query solution which works very well.

If we could do the same with Power Pivot and DAX, it would be perfect.
In fact, I wanted to create several Pivot Tables because in reality I would have another table that would be added, for example "Group" table (see example file).
The pivot tables are more flexible and we can easily insert blank lines after each item, it's a bit more complicated with Power Query.

I've put an example using this time a Pivot Table with the item "Group" and inserting an blank line
If it's not possible with DAX, I'll use your Power query solution, that's fine too.

Thanks again to you for helping me with my problem.

Good night.
 

Attachments

GraH - Guido

Well-Known Member
@Kamo, no worries, there are days I can't even make it to the forum. There is a life outside, right?

Good to read you like the PQ. Like I've said, I wish I knew how, but I'm in the dark for offering a PP solution that fits your demand. Allow me to repeat a wise dude's message to show some more patience.

Take care.
 

Chihiro

Excel Ninja
Looking for something like this? Personally, I prefer not to complicate DAX for sake of just the display... but I get that sometimes you want to condense data.

DAX measures:
Code:
Failed On :=
IF (
    HASONEFILTER ( Amount[Fallen On (Year)] ),
    SUMX ( Amount, IF ( Amount[Fallen On] <= Amount[Export to], Amount[Amount] ) ),
    BLANK ()
)
Code:
Fallen On :=
IF (
    HASONEFILTER ( Amount[Fallen On (Year)] ),
    IF (
        SUMX ( Amount, Amount[Amount] - [Failed On] ) = 0,
        BLANK (),
        SUMX ( Amount, Amount[Amount] - [Failed On] )
    ),
    BLANK ()
)
Code:
Total Amount :=
IF (
    HASONEFILTER ( Amount[Fallen On (Year)] ),
    BLANK (),
    SUM ( Amount[Amount] )
)
Then structure your Pivot table like below.
70752

See attached.
 

Attachments

Kamo

New Member
Hi Chihiro,

Thank you very much for this DAX solution, it's perfect,
That's what I wanted to get

I have 2 interesting solutions : GraH - Guido's with Power Query and yours with Power Pivot.

I'll look at the codes in more detail and apply them to my real file.

Thank you again Chihiro and Grah - Guido :)

Good night.
 

Kamo

New Member
@GraH - Guido ,
I've tested both solutions,
When I change a date to 2021, the Chihiro's solution automatically adds this column,
But with Power Query, it didn't work.

I made a small change to your query to correct this error.
It now works correctly, see the attached file for information

Good night.
 

Attachments

GraH - Guido

Well-Known Member
Hi, thank you for the feedback. Good sport.
I believe I was using a filter this year and before this year, which for the data at hand worked. Good you found a way to deal with dates in the future.
shows again why sample data needs to contain all possible scenario's ;)
 
Top