Need Dax for Start & End Date tabular Representation

[umarshehbaz]

Hi,

I have following data and I want to represent in below formate. I want to add date filter on top on the basis of transaction date.

Data Avaliable : 

ID	Leave Type	RemainingBalance	Transaction Date
2283	Annual Leave	30	1/1/2020
2283	Annual Leave	-1	2/20/2020
2283	Annual Leave	-1	2/21/2020
2283	Annual Leave	-1	2/25/2020
2283	Annual Leave	-1	4/27/2020
2283	Annual Leave	-1	4/28/2020
2283	Sick Leave	0	1/1/2020
2283	Sick Leave	-1	1/10/2020
2283	Sick Leave	-1	3/2/2020
2283	Sick Leave	-1	3/9/2020
2283	Sick Leave	-1	3/10/2020
2283	Sick Leave	-1	3/11/2020
2283	Sick Leave	-1	4/6/2020
2283	Sick Leave	-1	5/4/2020
2283	Sick Leave	-1	5/5/2020
2283	Sick Leave	-1	5/6/2020
2283	Unpaid Leave	0	1/1/2020

Need Output like : 

ID	Leave Type	Start Date	End Date	Leaves	Remaining Balance
2283	Annual Leave	1/1/2020	1/1/2020	30	30
2283	Annual Leave	2/20/2020	2/21/2020	-2	28
2283	Annual Leave	2/25/2020	2/25/2020	-1	27
2283	Annual Leave	4/27/2020	4/28/2020	-2	25
2283	Sick Leave	1/1/2020	1/1/2020	0	0
2283	Sick Leave	1/10/2020	1/10/2020	-1	-1
2283	Sick Leave	3/2/2020	3/2/2020	-1	-2
2283	Sick Leave	3/9/2020	3/11/2020	-3	-5
2283	Sick Leave	4/6/2020	4/6/2020	-1	-6
2283	Sick Leave	5/4/2020	5/6/2020	-3	-9
2283	Unpaid Leave	1/1/2020	1/1/2020	0	0

[v-kelly-msft · Accepted solution]

Hi  @umarshehbaz ,

You need 4 measures as below:

_measure = 
var _mindate= MINX(FILTER(ALL('Table'),'Table'[Leave Type]=MAX('Table'[Leave Type])&&'Table'[ID]=MAX('Table'[ID])&&'Table'[Index]<MAX('Table'[Index])&&'Table'[Transaction Date]=MAX('Table'[Transaction Date])-1),'Table'[Transaction Date])
Return
IF(_mindate=BLANK(),MAX('Table'[Transaction Date]),_mindate)

_Startdate = 
var _nextstartdate=CALCULATE([_measure],FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])+1))
Return
IF((MAX('Table'[Transaction Date])=[_measure])&&MAX('Table'[Transaction Date])<>_nextstartdate,'Table'[_measure],IF(_nextstartdate=MAX('Table'[Transaction Date])||_nextstartdate=[_measure],BLANK(),CALCULATE('Table'[_measure],FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])-1))))

_Leaveremaining = 
var _startdate=[_Startdate] return
IF(ISBLANK(_startdate),BLANK(),CALCULATE(SUM('Table'[RemainingBalance]),FILTER(ALLEXCEPT('Table','Table'[ID],'Table'[Leave Type]),'Table'[Transaction Date]>=_startdate&&'Table'[Transaction Date]<=MAX('Table'[Transaction Date]))))

_Remaining Balance = IF(ISBLANK('Table'[_Startdate]),BLANK(), CALCULATE(SUM('Table'[RemainingBalance]),FILTER(ALL('Table'),'Table'[Transaction Date]>=[_Startdate]&&'Table'[Transaction Date]<=MAX('Table'[Transaction Date])&&'Table'[Leave Type]=MAX('Table'[Leave Type]))))

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@umarshehbaz , Sontion, before leave dates are combined

https://www.dropbox.com/s/i2wzkmvb7g31job/leaveDates.pbix?dl=0

Checking on combining leave dates

[umarshehbaz]

Balance should be merged and end date as well please check