How to use a user input date serial number in another calculated column

[iristele]

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.

Base Date Value = SELECTEDVALUE('Base Date'[Date (Serial Number)])

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?

[OwenAuger]

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

[iristele]

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

[OwenAuger · Accepted 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.

1. The 1st argument of SUMX would be the table defining the granularity. Each row of this table would be applied as a filter.
2. The 2nd argument would be the expression to be summed, in this case the expression involving the measure and columns of the Opportunity Matrix table.. 

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

[iristele]

Hi @OwenAuger ,

Yes I think it will work. Thank you very much!