Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi!
I wanted to let the user to input a date and do a column calculation based on that date.
To achieve this, I added a parameter slicer, where the user input number would be the serial number of the target date, as we are only allowed to input a whole/decimal number but not a date as a parameter. The parameter table was Base Date, the series column was Date (Serial Number), and the selected value was captured in Base Date Value.
However, when I used Base Date Value in calculation of a column, the calculation seemed not working:
Here, I want to compute, under each contract, the total spend amount of invoices whose Final Approval Month (which is a date in fact) is within the 365 days before the BASE DATE selected by user. I calculate the difference between the serial number of the approval date and the input serial number and compare it with 0 and 365.
Basically, the calculation gives me a blank column.
Just for testing purpose, if I use TODAY() as the base date and compute the column as followed, it works and gives me some values.
How can I use the Best Date Value in the calculation of the column to achieve my goal here?
Solved! Go to Solution.
Hi again @iristele
If you could post a data model diagram (or possibly a sanitised PBIX) that may help.
But I'll try my best based on what you've described 🙂
Generally, if you want to sum a measure at some level of granularity, you would use SUMX.
I'm guessing a bit here, but if Opportunity Matrix is a table that would filter Invoices, then possibly something like this:
Potential Improvement =
SUMX (
'Opportunity Matrix',
[Total Spend in Last 365 Days (BD) SN]
* ( 'Opportunity Matrix'[Best Contract Term Across BU] - 'Opportunity Matrix'[Payment Term Standardized] )
/ 365
)
This measure would sum the expression for every row of Opportunity Matrix.
Does this help at all?
Regards
Hi @iristele
The short answer is that calculated columns cannot depend on filters applied in the Power BI report.
Calculated columns are normally "processed" after dataset refresh, and are not influenced by the content of any report that may depend on the dataset.
To use Best Date Value in a calculation, you must write a measure that references Best Date Value.
For the situation you've described, I would recommend creating a measure with similar code to your first calculated column screenshot. Something like:
Total Spend in Last 365 Days (BD) SN =
VAR BaseDateValue = [Base Date Value]
RETURN
CALCULATE (
SUM ( 'Invoices'[Invoices Invoice Line Reporting Total] ),
BaseDateValue - 'Invoices'[Invoices Invoice Final Approval Month] <= 365,
BaseDateValue - 'Invoices'[Invoices Invoice Final Approval Month] > 0
)
Does this work for you?
Regards
Hi @OwenAuger !
Oh you are right, I should not make a calculated column if I want to use the user input.
However there will be some following problems:
I need to use this calculated measure to calculate another measure Potential Improvement. It used to be a calculated column as well, but in this case I need to change it to a measure. In the table Opportunity Matrix, every row is a Contract, and I intend to calculate total spend in the last 365 days under that contract * a difference between best payment term across Business Units and the current Contract payment term then sum them up as the measure.
Potential Improvement = SUM('Opportunity Matrix'[Total Spend in Last 365 Days (BD) SN] * ('Opportunity Matrix'[Best Contract Term Across BU] - 'Opportunity Matrix'[Payment Term Standardized]))/365
I know that I should specify an aggregation, but I don't know how to do it properly... SUM() only takes in a column but not an expression like this.
(Best Contract Term Across BU and Payment Term Standardized are two columns.)
Hi again @iristele
If you could post a data model diagram (or possibly a sanitised PBIX) that may help.
But I'll try my best based on what you've described 🙂
Generally, if you want to sum a measure at some level of granularity, you would use SUMX.
I'm guessing a bit here, but if Opportunity Matrix is a table that would filter Invoices, then possibly something like this:
Potential Improvement =
SUMX (
'Opportunity Matrix',
[Total Spend in Last 365 Days (BD) SN]
* ( 'Opportunity Matrix'[Best Contract Term Across BU] - 'Opportunity Matrix'[Payment Term Standardized] )
/ 365
)
This measure would sum the expression for every row of Opportunity Matrix.
Does this help at all?
Regards
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |