Forum Discussion
Capture Category as per selected date dynamically
Dear Team,
Belwo is my source data. we have two slicers, Date1 SLicer adn Date 2 Slicer.
When i select Date 1 Slicer then Category of that particular ID and RO Value should be captured.
When i select Date 2 Slicer then Category of that particular ID and RO Value should be captured.
Delta should be processded R&O value at Date2 - R&O value at Date1
INPUT
| DATE | ID | Category | R&O Value |
| 1-Nov-23 | RO12 | BLUE | 100 |
| 1-Nov-23 | RO13 | CLOSED | 0 |
| 1-Nov-23 | RO14 | BLUE | 200 |
| 1-Nov-23 | RO15 | IMPLEMENTED | 0 |
| 2-Nov-23 | RO12 | BLUE | 200 |
| 2-Nov-23 | RO13 | BLUE | 100 |
| 2-Nov-23 | RO14 | BLUE | 200 |
| 2-Nov-23 | RO15 | BLUE | 300 |
| 3-Nov-23 | RO12 | CLOSED | 0 |
| 3-Nov-23 | RO13 | BLUE | 0 |
| 3-Nov-23 | RO14 | IMPLEMENTED | 300 |
| 3-Nov-23 | RO15 | BLUE | 400 |
| 3-Nov-23 | RO16 | BLUE | 350 |
SLICER
| Date1 -SLICER | Date2-SLICER |
| 1-Nov-23 | 3-Nov-23 |
OUTPUT
| ID | Category at Date1 | Category at Date2 | R&O value at Date1 | R&O value at Date2 | Delta |
| RO12 | BLUE | CLOSED | 100 | 0 | -100 |
| RO13 | CLOSED | BLUE | 0 | 0 | 0 |
| RO14 | BLUE | IMPLEMENTED | 200 | 300 | 100 |
| RO15 | IMPLEMENTED | BLUE | 0 | 400 | 400 |
| RO16 | BLUE | 0 | 350 | 350 |
Regards
Reddy.
Sorry I don't have a version of Desktop that is that old. But my data model is pretty simple
I made the relationship to Calendar2 inactive.
Then the technique I used with the "Date2" measures was the following pattern
Category at Date2 = CALCULATE(max( 'DataTable'[Category]),USERELATIONSHIP('DataTable'[DATE], Calendar2[Date]),all('Calendar'))R&O Value at Date2 = CALCULATE(sum( 'DataTable'[R&O Value]),USERELATIONSHIP('DataTable'[DATE], Calendar2[Date]),ALL('Calendar'))
5 Replies
No solution for m requirmetn ?
So you would need to create a date table for each slicer, the attached pbix file achieves the following
Dear Gosbell,
thankyou somuch for your support.
Am facing the below error. which version you have used to developed this soolution. as now am using Report Server vesion Jan2023
Please let me know how can i opne the file.
Regards,
Reddy.
Sorry I don't have a version of Desktop that is that old. But my data model is pretty simple
I made the relationship to Calendar2 inactive.
Then the technique I used with the "Date2" measures was the following pattern
Category at Date2 = CALCULATE(max( 'DataTable'[Category]),USERELATIONSHIP('DataTable'[DATE], Calendar2[Date]),all('Calendar'))R&O Value at Date2 = CALCULATE(sum( 'DataTable'[R&O Value]),USERELATIONSHIP('DataTable'[DATE], Calendar2[Date]),ALL('Calendar'))all working fine, but when i remove ID all values are showign 0. can we show those values with out ID column ?
