Capture Category as per selected date dynamically

[tangutoori]

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.

[d_gosbell · Accepted solution]

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')

)

[tangutoori]

all working fine, but when i remove ID all values are showign 0. can we show those values with out ID column ?

[d_gosbell]

So you would need to create a date table for each slicer, the attached pbix file achieves the following

[tangutoori]

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.

[tangutoori]

No solution for m requirmetn ?