Power Pivot with expiry and not expiry date

[Kamo]

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

 

[Chihiro]

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]

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

 

[pecoflyer]

Cross post without links

Pivot Table modification

Hi, Please excuse me if the subject is not in the right thread, I would like that if the invoice dates with the "Fallen On" column greater than the "Export to" column then to indicate the amounts in a new "Not failed" column. I currently made the Pivot table using Power Pivot...

www.excelguru.ca

 

[vletm]

Kamo
You should reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

Those are for You too.

 

[Kamo]

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]

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.

 

[Kamo]

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]

@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]

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]

Kamo
See if this works?

 

[Kamo]

Kamo
See if this works?

Hi Grah - Guido,

Thank you very much for your response,

Your solution seems to work, but how to delete columns with a total amount equal to 0 for all years (see attached picture)

Thank you in advance

 

[GraH - Guido]

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]

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]

Hi again,

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

 

[Kamo]

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]

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.



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

 

[GraH - Guido]

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.

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

 

[Kamo]

@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.

 

[GraH - Guido]

@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]

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:

Failed On :=
IF (
    HASONEFILTER ( Amount[Fallen On (Year)] ),
    SUMX ( Amount, IF ( Amount[Fallen On] <= Amount[Export to], Amount[Amount] ) ),
    BLANK ()
)

Fallen On :=
IF (
    HASONEFILTER ( Amount[Fallen On (Year)] ),
    IF (
        SUMX ( Amount, Amount[Amount] - [Failed On] ) = 0,
        BLANK (),
        SUMX ( Amount, Amount[Amount] - [Failed On] )
    ),
    BLANK ()
)

Total Amount :=
IF (
    HASONEFILTER ( Amount[Fallen On (Year)] ),
    BLANK (),
    SUM ( Amount[Amount] )
)

Then structure your Pivot table like below.


See attached.

 

[Kamo]

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]

@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.

 

[GraH - Guido]

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

 

[Kamo]

shows again why sample data needs to contain all possible scenario's

Hi Grah- Guido,
Strongly Agree

Sincerely