Forum Discussion
how to filter data based Run date is relative date Compare date is null or relative date
Hello,
My aggregate table is like:
| Date | Comapny Name | Total Counts | Live counts | Inactive counts | changes in live counts | changes in inactive counts |
| 30-june | PQR | 100 | 80 | 20 | 0 | 0 |
| 1-july | ABC | 80 | 40 | 40 | 0 | 0 |
| 1-july | PQR | 95 | 90 | 5 | 0 | 0 |
| 2-july | ABC | 120 | 20 | 100 | 0 | 0 |
| 2-july | PQR | 95 | 90 | 5 | 0 | 0 |
Now, i create two date filters from date column 1) Run date 2) Compare date
Note : always Compare date should be equal or lesser than the run date in date filter.
Changes column work like run date counts - compare date counts
example 1: My run date is 2-july and compare date is 1- july, i want result like:
| Date | Company Name | Total Counts | Live Counts | Inactive counts | changes in live counts | changes in inactive counts |
| 2-july | ABC | 120 | 20 | 100 | -20 | 60 |
| 2-july | PQR | 95 | 90 | 5 | 0 | 0 |
example 2: My run date is 1-july and compare date is 30- june, i want result like:
| Date | Comapny Name | Total Counts | Live counts | Inactive counts | changes in live counts | changes in inactive counts |
| 1-july | ABC | 80 | 40 | 40 | 0 | 0 |
| 1-july | PQR | 95 | 90 | 5 | 10 | -15 |
Concenr is: for example 2 dates Company ABC data is not showing in table.
1 Reply
- amitchandak
Super User
Shloka_Lakhani , with help from date table you can create DOD measures
example
This Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('Table'[Qty]), previousday('Date'[Date]))Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9or do it using two selections?
Power BI How to use/compare two Date/Period slicers: https://youtu.be/WSeZr_-MiTg
