Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a couple of tables in my model.
I want to create a table (or matrix) that essentially shows the information below.
For each customer, for each year, I want to show the running total invoiced, but only up until today.
I do not need the TOT 2016 column in my visual. I have added it here for information purposes only.
Assume today is 06-06-2017, then RT16 should be calculated as the sum of all invoices from 01-01-2016 until 06-06-2016.
I have created a number of measures to achieve this result, but none seem to work for me. (All of them do calculate RT17 correctly, but I am strugelling to get my measure correct for previous years. They always seem to calculate the total over 2016 and never only part of the year.
I would think this measure should work, but.... ehhhh... nope... (placing the measure in the Customer table.
RT = CALCULATE ( SUM ( 'Invoice'[Amount] ); FILTER ( 'Invoice'; 'Invoice'[Postingdate] <= 'DateTable'[Date]
&& 'Invoice'[Postingdate] >= STARTOFYEAR ( 'DateTable'[Date] ) ) )
I would think this should work and it works fine for 2017, but not for previous years.
Solved! Go to Solution.
I found a solution that works and even works year-over-year-over-year-over-year
First I have created an extra column on my Invoice table with the following formula:
InvoiceDayNBR = 'Invoice'[Postingdate] - DATE( YEAR( 'Invoice'[Postingdate]); 1; 1) +1
This gives me the daynumber in the current year.
Next I created the following measure on my customer table:
RT = CALCULATE( SUM( 'Invoice'[Amount]);
FILTER( 'Invoice'; ( TODAY() - DATE( Year( TODAY() ); 1; 1) +1) > 'Invoice'[InvoiceDayNBR]) )
Adding this measure to a matrix with the customer table[name] as rows and DateTable[Date] (years only) as colums, I get the following result:
The result I was aiming for:
The result I was getting so far:
Notice that in the correct result, there is no value for 2016. This is correct, since all sales to customer ADC is in october and november of 2016. There is also slight variations in 2010 and 2011, since in those two years there are sales in the last months of the year.
Still my solution feels very clunky and I would need to create a different measure to get this working for months as well. So if someone would be able to provide a more felxible solutions, I am all ears.
You may wonder, why did he not just use the STARTOFYEAR function to calculate the first day of the year?
STARTOFYEAR( 'Invoice'[Postingdate] ) gives me the first postingdate in the year of postingdate, which nearly never is 1-1-####. For instance in 2017 my first posting is on January 2nd, so for any postingdate in 2017, I get 02-01-2017 as the first day of the year. In 2015, my first posting is on March 15th, so for any postingdate in 2015, STARTOFYEAR returns 15-03-2105.
Which brings me to a second question: Is this how STARTOFYEAR is supposed to work? Which function should I be using to determine the first of january of a given year based on a random date?
I found a solution that works and even works year-over-year-over-year-over-year
First I have created an extra column on my Invoice table with the following formula:
InvoiceDayNBR = 'Invoice'[Postingdate] - DATE( YEAR( 'Invoice'[Postingdate]); 1; 1) +1
This gives me the daynumber in the current year.
Next I created the following measure on my customer table:
RT = CALCULATE( SUM( 'Invoice'[Amount]);
FILTER( 'Invoice'; ( TODAY() - DATE( Year( TODAY() ); 1; 1) +1) > 'Invoice'[InvoiceDayNBR]) )
Adding this measure to a matrix with the customer table[name] as rows and DateTable[Date] (years only) as colums, I get the following result:
The result I was aiming for:
The result I was getting so far:
Notice that in the correct result, there is no value for 2016. This is correct, since all sales to customer ADC is in october and november of 2016. There is also slight variations in 2010 and 2011, since in those two years there are sales in the last months of the year.
Still my solution feels very clunky and I would need to create a different measure to get this working for months as well. So if someone would be able to provide a more felxible solutions, I am all ears.
You may wonder, why did he not just use the STARTOFYEAR function to calculate the first day of the year?
STARTOFYEAR( 'Invoice'[Postingdate] ) gives me the first postingdate in the year of postingdate, which nearly never is 1-1-####. For instance in 2017 my first posting is on January 2nd, so for any postingdate in 2017, I get 02-01-2017 as the first day of the year. In 2015, my first posting is on March 15th, so for any postingdate in 2015, STARTOFYEAR returns 15-03-2105.
Which brings me to a second question: Is this how STARTOFYEAR is supposed to work? Which function should I be using to determine the first of january of a given year based on a random date?
@DcAssink i am little confused as to what you trying to say exactly
it really easier to understand if you provide an example of the problem in a visual and them provide what you expect in an example
I might be off the mark but i think you might need to use an iterator, such as sumx (as opposed to sum)
however if you provide the above , i can try and assist
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
109 | |
109 | |
93 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |