Table totals are correct but table line items missing so it doesn't add up to total

[Innesmacleod]

Good Evening, I hope you can help.

I have a visual that shows the bookcost of assets for the current month and the prior month. There is a slicer for each month end date and I have selected the current month.

The book cost total of the visual agrees for both the current month and the prior month however the line by line items for the prior month do not add up to the total as it is missing two assets that have been sold in the month. Prior month should have 90 lines and current month 88 lines.

Is there anyway to show all assets from both months and in the current month column show a nil bookcost?

Is it because I have the slicer set to current month so it ignores any asset that isn't in the current month?

I have tried a unique table of assets but that failed to produce the right result.

Visual Headers:

Comparative Bookcost = calculate(sum ('DVAL History'[Book Cost]), dateadd('DVAL History'[Valuation Date],-1,month))

Month end bookcost = calculate(sum('DVAL History'[Book Cost]), ENDOFMONTH('DVAL History'[Valuation Date]))

Asset code comes from DVAL history

The slicer uses the month end valuation date from the fact table from the same Dval History sheet that the bookcost is taken.

Any help you can provide would be appreciated.

Thanks in advance,

Innes

[jennratten]

Hello - there are other factors to consider such as any other slicers, filters, measures on the page.  That being said, here is a good example.  You should add a date table to your data model, relate the date in the date table to the Valuation Date column in your data table and use the date field from the date table in your measures and visuals.  If you wanted to show the value as the end of the month, you would add a column to your date table for the end of the month (for each row).

New Measure for Current Period: 

$ Book Cost:= sum('DVAL History'[Book Cost])

New Measure for Prior Period:

$ Book Cost PP :=
-- Replace [Year Month Number] with the column from the date table that is in the slicer.
VAR CurrentYearMonth = SELECTEDVALUE ( 'Date'[Year Month Number] )
VAR PreviousYearMonth =
    CALCULATE (
        MAX ( 'Date'[Year Month Number] ), 
        ALLSELECTED ( 'Date' ),
        KEEPFILTERS ( 'Date'[Year Month Number] < CurrentYearMonth )
    )
VAR Result =
    CALCULATE (
        [$ Book Cost],
        'Date'[Year Month Number] = PreviousYearMonth,
        REMOVEFILTERS ( 'Date' )
    )
RETURN
    Result

https://www.sqlbi.com/articles/comparing-with-previous-selected-time-period-in-dax/  

[Innesmacleod]

Good evening,

Thank you very much for the above; I've added the date table as you suggested - linking the date table "date" to the fact table "valuation date". Screen shot below.

The sliver now uses the monthending column from the date table instead of the valuation date from the fact table.

I've updated your formula for my column names.

The issue I now have is that the data isn't pulling any values through for the Prior Month Bookcost test column.
I think I have followed and adapted your formula correctly (but obviously not).

Would you be able to advise if there is something obvious I have missed?

If there is anything else I can provide to help please let me know.

Thank you again,

Innes

Prior Month end bookcost test =

-- Replace [Year Month Number] with the column from the date table that is in the slicer.

VAR CurrentYearMonth = SELECTEDVALUE ( 'Date Table'[MonthEnding] )

VAR PreviousYearMonth =

CALCULATE (

MAX ( 'Date Table'[MonthEnding] ),

ALLSELECTED ( 'Date Table' ),

KEEPFILTERS ( 'Date Table'[MonthEnding] < CurrentYearMonth )

)

VAR Result =

CALCULATE (

[Book cost Test],

'Date Table'[MonthEnding] = PreviousYearMonth,

REMOVEFILTERS ( 'Date Table' )

)

RETURN

Result