Forum Discussion
Slicer not working in table
Hi,
I've got a table like below. I managed to get [Closing Balance], [Opening Balance] and [Running Balance] working, as is shown in the table.
However, when I add a slicer (For example, [MonthEnd] or [Month]) and tried to filter the month I want, the [Opening Balance Final] is always blank for some reason. Any idea how it may happen?
For your reference, below are my calculations for my [Closing Balance Final] and [Opening Balance Final].
7 Replies
- amitchandak
Super User
First of create a date table and move your date related calculation there. Also try to prefer datesmtd, totalmtd, datesytd etc for your calculation.
Closing Balance Final = CALCULATE ( [YTD Amount], FILTER ( all('Date'), [MonthEnd] <= MAX ( 'Date'[MonthEnd] ) ),[Report] = "Balance Sheet" ) Opening Balance Final = CALCULATE ( [YTD Amount], FILTER ( ALLSELECTED('Date'), 'Date'[MonthEnd] <= MAX ( 'Date'[PrevMonthEnd] ) ),[Report] = "Balance Sheet" )How to use time intelligence. Example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH)))) last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)))) MTD (Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date]))) MTD (Last Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31"))) Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)) Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH)) 3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH))) QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date]))) Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER))) Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER))) Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year))) YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year)) 2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year)) Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date]))) Cumm Sales = CALCULATE(SUM('Fact table'[Sales]),filter(date,date[date] <=maxx(date,date[date]) && weekday(sales[date]) <=weekday(maxx(date,date[date])) )) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-2,Year)),"12/31"))In case you only have month and year
date = date(year(table[year]),month(table[month],1)
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.
Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on LinkedinThanks for the reply!
I have a Date table, in my fact table I have [Date], [MonthEnd], [PrevMonthEnd], [Week Ending], all of Date type. When I create a relationship between the Date and the fact table, which date column should I join?
Hi,
If I do it your way, [Closing Balance Final] is correct, however [Opening Balance Final] became the same as the closing balance, it's not returning the number for the previous month...
- amitchandak
The opening should be cumulative till last month-end date ??
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,endofmonth(dateadd(date[date]),-1,month))))
Or last date total
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] =maxx(date,endofmonth(dateadd(date[date]),-1,month))))
use all(date), if needed
