Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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/
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
85 | |
82 | |
65 | |
49 |
User | Count |
---|---|
138 | |
110 | |
104 | |
66 | |
64 |