Forum Discussion
PowerBI-Newbie
Helper IV
Helper IVHelp with Multiple Filters from User Selection Slicers
Hi, I have a calendar table which has columns Year and Period that I'm using as slicers: I'm using these to filter out data from 6 data tables; these 6 tables also contain the columns Year...
A bit tricky, but a disconnected table trick and this measure later and I think I got it. PBIX is attached:
SIP Actual Line Chart = VAR __Year = MAX('SIP_Activities'[Year]) VAR __Period = MAX('SIP_Activities'[Period]) + 0 VAR __LinePeriod = MAX('Periods'[Period]) + 0 VAR __Table = FILTER(ALL(SIP_Activities),[Year] = __Year && [Period]+0 <= __Period) VAR __Table1 = FILTER(__Table,[Period]+0 = __LinePeriod) RETURN SUMX(__Table1,[Actual-LD]) + SUMX(__Table1,[Late Delivery])
amitchandak
Super User
Super UserPowerBI-Newbie , In case you have date, prefer using time intelligence
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((Table[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR(Table[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd(Table[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd(Table[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd(Table[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Year))
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/
PowerBI-NewbieHi amitchandak ,
Thank you for your response.
Unfortunately we don't use date for this particular dashboard, it's only Fiscal Year and Period as per my data.