Capture date from Date Slicer

[BrianJ]

@axaeffect,

OK, two major problems with your Aging measure:

1. because you are changing filter context (to ALLSELECTED), you need to wrap this in a CALCULATE statement; and
2. if I understand what you’re trying to do here correctly, SUMX is not needed.

Here’s the rewritten measure:

Aging = 
    CALCULATE(
        DATEDIFF(
            SELECTEDVALUE( FactInternetSales[DueDate] ),
            [SelectedDate],
            DAY
        ),
        ALLSELECTED(
            DimDate[FullDateAlternateKey]
        )
    ) 

As you can see from the screenshot below, this works fine except for the fact that it doesn’t produce a grand total, because there’s no evaluation context for the total line.

In order to get the proper grand total, you need a more complex version of the same measure:

Aging with Total = 

VAR VirtualTable =
    CALCULATETABLE(
        ADDCOLUMNS(
            FactInternetSales,
            "AgingVirt", [Aging]
        ),
        ALLSELECTED( DimDate[FullDateAlternateKey] )
    )
RETURN
IF( 
    HASONEVALUE( FactInternetSales[SalesOrderNumber] ),
    [Aging],
    SUMX (
        VirtualTable,
        [AgingVirt]
    )
)

For more information on how to get correct totals, check out this recent post which has links to a number of excellent videos by Sam explaining this issue in depth.

Power bi divide formula is averaging out the result instead of Sum

@nbaraili, Just need to apply a bit of extra logic in the DAX measure to get the correct total. This revised measure should work for you: Price Revised Logic = VAR BasePriceTable = // This creates a virtual table from which we can calculate the correct total price ADDCOLUMNS( 'Product', "BasePriceCol", [Price] // This is your measure ) VAR TotalBasePrice = // This calculates the sum of the price column in our virtual table SUMX( BasePriceTable, [BasePriceCol] ) RETURN …

Full solution file attached below.

Hope this is helpful.

• Brian
  Testing_AgingAdventureWorksDW_Import - solution.pbix (737.7 KB)