Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am trying to figure out a way to calculate the difference in balances when I select multiple dates in my slicer. For example, if balance in 12/31/2023 was 11m and balance in 1/31/2024 is 12m I would like to see a difference of 1m be the output.
Solved! Go to Solution.
Hi,
Create a Calendar Table and a retionship (Many to One and Single) from the ProdDate column of your Data Table to the Date column of the Calendar Table. To your slicer, drag the Date column from the Calendar Table and select 2 dates. Write these measures
Total = sum(Data[Amount])
Total on first date = calculate([Total],datesbetween(Calendar[date],min(calendar[date]),min(calendar[date])))
Total on second date = calculate([Total],datesbetween(Calendar[date],max(calendar[date]),max(calendar[date])))
Variance = [Total on first date]-[Total on second date]
Hope this helps.
It's not wrong the number on the card visual. It's the context where DAX is being evaluated.
If I select 31-01-2024 date on my table the card will show the correct number like this:
The Dax measure I used it's to calculate the Balance of the previous month of each date that you have on the visual. It's working to the table matrix and bar chart.
Proud to be a Super User!
Hi,
Create a Calendar Table and a retionship (Many to One and Single) from the ProdDate column of your Data Table to the Date column of the Calendar Table. To your slicer, drag the Date column from the Calendar Table and select 2 dates. Write these measures
Total = sum(Data[Amount])
Total on first date = calculate([Total],datesbetween(Calendar[date],min(calendar[date]),min(calendar[date])))
Total on second date = calculate([Total],datesbetween(Calendar[date],max(calendar[date]),max(calendar[date])))
Variance = [Total on first date]-[Total on second date]
Hope this helps.
I fixed the measures. However; my card that I want to show for the variance does not pull back correct number. IT only returns the balance for the max date but not the difference between the min and max date selected in slicer.
See Below:
Share the download link of the PBI file and show the problem there clearly.
Total on first date and total on second date are invalid functions. It says "too few arugments were passed to the DATESBETWEEN function.
Hi,
As far as I can see, you are using implicit measures, and to do what you want you need to use explicit measures.
I did a quick example in my pbi desktop and have this results:
I'm using this two measures:
Proud to be a Super User!
It returns this. I want the 116,899.82 to show up in the card but it shows the grand total instead in the card which is way off by the way.