DAX - Calculate daily amount between rate table and daily transaction table

[Anonymous]

I have a transaction table (Items Sold) that has daily sum for total items per day.

I also have a rate table that tracks rate changes (Effective Start Date and Effective Stop Date). For this example, the rate is per Item.

I need a calculated measure that will do the following logic:  Rate per Item * Total Items (Rate should be selected based on Effective Start & Stop). If there isn't a record for Total Items in the Items Sold table, I would want to see $0.

Below are table samples and the highlighted result is what this measure needs to do.

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

According to my understand ,you want to calculate Arithmetic product filtered from two tables， right?

You could use the following formula:

Measure 2 =
VAR tab =
    ADDCOLUMNS (
        'Item Sold',
        "Result",
            VAR _date = [Date]
            VAR _loc = [Location]
            VAR _total = [Total]
            RETURN
                SUMX (
                    FILTER (
                        ALL ( Rates ),
                        OR (
                            NOT ( ISBLANK ( [Effective Stop Date] ) )
                                && [Effective Start Date] <= _date
                                && [Effective Stop Date] >= _date
                                && [Location] = _loc,
                            _date >= [Effective Start Date]
                                && ISBLANK ( [Effective Stop Date] )
                        )
                    ),
                    [Rate] * _total
                )
    )
RETURN
SUMX ( tab, [Result] )

My visualization looks like this:

Or you could merge the two tables in Query Editor, then use:

Measure =
IF (
    MAX ( 'Merge1'[Rates.Effective Stop Date] ) <> BLANK (),
    IF (
        MAX ( 'Merge1'[Date] ) >= MAX ( 'Merge1'[Rates.Effective Start Date] )
            && MAX ( 'Merge1'[Date] ) <= MAX ( 'Merge1'[Rates.Effective Stop Date] ),
        1,
        0
    ),
    IF (
        MAX ( 'Merge1'[Date] ) >= MAX ( 'Merge1'[Rates.Effective Start Date] ),
        1,
        0
    )
)

Daily Amount =
MAX ( 'Merge1'[Total] ) * MAX ( 'Merge1'[Rates.Rate] )

Apply Measure to filter (set as “is 1”),My visualization looks like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[amitchandak]

@Anonymous , Get the new column in item table

minx(filter(Rate,item[Date]>= Rate[Start Date] && (isblank(Rate[end date]) || item[date]>=Rate[end date]) && Item[Location] =Rate [Location]),Rate[Rate])

you can multiple with Total

[Anonymous]

@Greg_Deckler - could the lookupvalue function be used to handle this? I need blanks (records don't exist for that date in the table) to be filled in with 0

One more question came out of doing this. I created a measure to multiply the Rate*Item Total, showing 0 if it's blank. However, I have a date filter on the chart that only shows current month (but could be changed to show previous month). The problem I'm facing is the total is not limited to the date in the scope of the selected date filter. 

I created a calculated column to work around this, but that won't show $0 if there is a blank value.

Ideally I want the daily showing like Calculated Measure, but the MTD should show the total that's on the Calculated Column line.

Calculated Measure= if(isblank(SUM('Items'[Rate])*SUM('Items'[TotalItems])),0,SUM('Items'[Rate])*SUM('Items'[TotalItems]))

Calculated Column= 'Items'[Rate]*'Items'[TotalItems]

[Greg_Deckler]

@Anonymous No, you can't use LOOKUPVALUE for looking up between ranges, that's why I created LookupValue Range - https://community.powerbi.com/t5/Quick-Measures-Gallery/LOOKUPVALUE-Range/m-p/974201#M430

You could do the lookup range to a VAR and then check it like:

VAR __Calc = <lookup range logic>
VAR __FinalCalc = IF(ISBLANK(__Calc),0,__Calc)

Not sure I am clear on the second question. 

[Anonymous]

@Greg_Deckler  I guess I'm still struggling with what the Var __Calc would be. Do I first create them as calculated columns as suggested by @amitchandak ?  I don't think that calculation is working right with the effective dates.

[Greg_Deckler]

@Anonymous - Sorry, I'll try to be more specific:

Measure =
  VAR __Date = MAX([Date])
  VAR __Location = MAX([Location])
  VAR __Rate =
    MAXX(
      FILTER(
        'Rates',
        [Location]=__Location &&
           [Effective Start Date] >= __Date &&
             [Effective Stop Date] <= __Date
      ),
      [Rate]
    )
  VAR __FinalCalc = IF(ISBLANK(__Rate),0,__Rate)
RETURN
  __FinalCalc