Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Group installments by date

Hi everyone, I've been having a problem lately with a report I am trying to build...

 

So I've prepared this sample data in order to illustrate my question:

 

Categorypayment1paydate1payment2paydate2payment3paydate3
a10jan/1927mai/1910jan/22
b20jan/19545jun/2120jan/19
c27dez/2110jan/1941jan/19
d545mai/2120jun/2114jan/22
e45mai/2130jan/2210jan/22
f27dez/2178jun/2120jan/22
g545mai/2110jun/2127jan/22
h87jun/212jun/210jan/19


I need to make sure that power bi knows that the paydate 1 is linked to payment 1, payment 2 to paydate 2 and so on...

Also, I want it to sum the category payments horizontally, not vertically, according to its date.

 

For example, when selecting category "a" and the year of 2019, I want to see the sum 10 +27 = 37.

 

Do I need to use dax for it?

 

Thanks in advance!

 

1 ACCEPTED SOLUTION

Hi, @Anonymous 

Thank you for your feedback.

 

Q1. There is no difference. I created the year column in Power Query Editor, and yours is created by Calculated Column, so that is why the symbol looks different.

 

Q2. In a general situation, I prefer to have a thin-fact-table. The first question's table was not that wide so I created a measure without connecting the tables. However, the second question's table was wider than the first one, so I wanted to transform it into a different structure to have a thin table. In this case, I could connect to the calendar table, and the measure is so simple to write. 

Please check the link down below.

The result is the same as the first one, but the measure is so simple, if you transform the table structure. Transforming the table structure is easily done in Power Query Editor.

 

Picture1.png

 

 

Payment Total V2 =
SUM(DataV2[payment])
 
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: https://www.linkedin.com/in/jihwankim1975/

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Jihwan, you absolutely rock!!

 

It was exactly what I was looking for!

 

I just have two more questions...

The first one is concerning the calendar you prepared in order to make it work:

So, I am used to create the date columns that I need, and the "year" one is always of good use, sure!

But, I've noticed that on your pbix file, the year in your calendar table appear as:

justchill_0-1618762555873.png with the ∑ symbol... Mine doesn't appear like that.

It shows like this (please note that I'm using "year" in pt here):

justchill_1-1618762816382.pngbut once I've added the dax function you recommended, it works as well.

I am wondering if the outcome gets affected somehow...

 

And the second one...

What would be the best way to add one more filter to classify these payments...

Using the same example table I've provided you with, the status columns to classify the payments can appear as:

 

justchill_4-1618763016379.png

I've uploaded the example table to dropbox like you did, I hope it helps:

 

https://www.dropbox.com/s/t5o7c859qi9xjfi/justchill.xlsx?dl=0

 

Thanks again!

 

 

Hi, @Anonymous 

Thank you for your feedback.

 

Q1. There is no difference. I created the year column in Power Query Editor, and yours is created by Calculated Column, so that is why the symbol looks different.

 

Q2. In a general situation, I prefer to have a thin-fact-table. The first question's table was not that wide so I created a measure without connecting the tables. However, the second question's table was wider than the first one, so I wanted to transform it into a different structure to have a thin table. In this case, I could connect to the calendar table, and the measure is so simple to write. 

Please check the link down below.

The result is the same as the first one, but the measure is so simple, if you transform the table structure. Transforming the table structure is easily done in Power Query Editor.

 

Picture1.png

 

 

Payment Total V2 =
SUM(DataV2[payment])
 
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: https://www.linkedin.com/in/jihwankim1975/

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

Hi, Jihwan, sorry I couldn't come back to it sooner!

 

It took me a while to get the modifications you have done with the tables into the power query editor.

In a way it really worked, but I was hoping you could help me modifying it to an easily updatable way...

Once I add another line to it, for example, with the "a" category, I get the 0x80040E4E error and another one saying I can't have duplicate value. 

 

Would the dax solution be an option for this updatable report? 

 

Also, would you happen to know if including a "source column", for example containing "internal", "external" and "pending" filterable text values to the dax formula would make it too complicated?

 

Thank you so much so far 😄

Hi, @Anonymous 

Sorry that it is quite difficult for me to understand your question.

If it is OK with you, could you please share your sample pbix file's link?

After I transform the fact table, there is only one simple measure applied which is below.

 

Payment Total V2 =
SUM(DataV2[payment])
 
 
I might miss something important concept in your case. 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

Sure thing!

What might be new to this idea is that I want it to be easily updatable/refreshable
Suppose I want to add different items to lines 11 and 12 in the excel worksheet and then save. I want to be able to click on the refresh power bi button, getting the update view report with these news itens included.

 

In short, the filters I'll be aplying will be: source and task (please note that some of them can now be repeated in the lines) and status ---> resulting the sum of payments done, like you've already solved in the first and second example I asked previously!


I've put it here: https://www.dropbox.com/s/08t2bwq7spul9fo/justchill.xlsx?dl=0

 

Thanks!!

Hi,

I think your current file and previous file are different. The previous one has 7 columns, and the current one has 4 more columns than the previous one.

So this is the reason why it cannot be updated by clicking the refresh button. If you have the same column names, same order, same number of columns (=same structure), then adding rows is very simple -> as you expected, you can simply click the refresh button.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

Exactly, it turned out to be a little bit more complex than I expected... 
Even by adding only new rows I keep getting these messages:

OLE DB or ODBC error: HRESULT exception: 0x80040E4E.

The 'Category' column in the 'Example (3)' table contains a duplicate 'a' value and this is not allowed for columns on one side of the many-to-many relationship or for columns being used as the primary key of a table.

It seems to me that the category cannot be repeated, but the new rows will certainly contain the same tasks eventually...

Considering the current excel file I uploaded, do I need to re-design the relationships in order to make it work? How would that look like considering the internal or external new filter options?

 

Thanks for your patience! 

Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

 

The measure is in the sample pbix file.

 

Picture1.png

 

Payment Total =
CALCULATE (
SUM ( Data[payment1] ),
FILTER (
Data,
Data[paydate1] >= MIN ( 'Calendar'[Date] )
&& Data[paydate1] <= MAX ( 'Calendar'[Date] )
)
)
+ CALCULATE (
SUM ( Data[payment2] ),
FILTER (
Data,
Data[paydate2] >= MIN ( 'Calendar'[Date] )
&& Data[paydate2] <= MAX ( 'Calendar'[Date] )
)
)
+ CALCULATE (
SUM ( Data[payment3] ),
FILTER (
Data,
Data[paydate3] >= MIN ( 'Calendar'[Date] )
&& Data[paydate3] <= MAX ( 'Calendar'[Date] )
)
)

 

 

https://www.dropbox.com/s/b3yi7ct34iqoqww/justchill.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.