Forum Discussion
How to show all preceeding data based on a selected date?
Hello,
I need to create visuals that dynamically change number of distinct counts based date selected.
I have quarterly files which is automatically loaded to Power BI. New files are appended and duplicates are removed so I don't need to store duplicate data.
Now I need to create visualizations in power bi.
1. Table showing the number of items per status - I would like the count to dynamically change based on my date selector.
2. Another sample visual is to show trend of status for each item per quarter
The idea is really to pull all items preceeding the selected quarter and count only items with the most recent date for that selected quarter. The dax functions I used seem to only filter the specific dates associated to the quarter but wouldn't give me the all data preceeding that quarter. Appreciate your help!!!!
Anonymous , You use time intelligence with help from date table, dates and period should be used from date table
QTD = CALCULATE(Max(Table[Status]),DATESQTD(('Date'[Date])))
Last QTD = CALCULATE(Max(Table[Status]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0Hi ,
A disconnected calendar is required.
You could create a measure like below and apply it to visual level filter.
filter_measure = IF(ISBLANK(MAX('Table'[Update]))||(MAX('Table'[Update]) <= MAX('date'[Date])),1,0)Then create a measure to count the status:
Measure 2 = COUNTROWS(FILTER('Table','Table'[Status] = CALCULATE(FIRSTNONBLANK('Table'[Status],0),FILTER(ALLEXCEPT('Table','Table'[Item]),'Table'[Update]<=MAX('date'[Date])))))Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous , You use time intelligence with help from date table, dates and period should be used from date table
QTD = CALCULATE(Max(Table[Status]),DATESQTD(('Date'[Date])))
Last QTD = CALCULATE(Max(Table[Status]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0Hi ,
A disconnected calendar is required.
You could create a measure like below and apply it to visual level filter.
filter_measure = IF(ISBLANK(MAX('Table'[Update]))||(MAX('Table'[Update]) <= MAX('date'[Date])),1,0)Then create a measure to count the status:
Measure 2 = COUNTROWS(FILTER('Table','Table'[Status] = CALCULATE(FIRSTNONBLANK('Table'[Status],0),FILTER(ALLEXCEPT('Table','Table'[Item]),'Table'[Update]<=MAX('date'[Date])))))Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.