Beginning and Ending value

[PBI5851]

Hello,

I am trying to generate a beginning value and ending value based on the below raw data. 

DateVal	Count	Flag
1/1/2021	100	Incoming
1/1/2021	-20	Reject1
1/1/2021	-10	Reject2
1/1/2021	-30	Reject3
2/1/2021	-5	Reject1
2/1/2021	-5	Reject2
2/1/2021	-10	Reject3
3/1/2021	20	Incoming
3/1/2021	-30	Reject1
4/1/2021	10	Incoming

I am hoping to get the below structure. 

	Jan	Feb	Mar	Apr
Beginnning		40	20	10
Incoming	100	0	20	10
Reject1	-20	-5	-30	0
Reject2	-10	-5	0	0
Reject3	-30	-10	0	0
Ending	40	20	10	20

I thought of using PreviousMonth ,by creating the Ending Measure, but the Previous Month cant be done on a Measure and also without the correct Beginning value for Feb, the Ending is incorrect. Any recommendations of how to resolve this please. 

Thank you. 

[PBI5851]

Please could you send me a pbix file using this solution. It doesnt work when i try it out. 

[amitchandak]

@PBI5851 , Create measure with help from date table

Beginnning = CALCULATE(sum(Table[Count]),filter(allselected(date),date[date] <=maxx(date,dateadd(date[date]),-1,Month)))
Ending = CALCULATE(sum(Table[Count]),filter(allselected(date),date[date] <=max(date[date])))

Reject1 = calculate(sum(Table[Count]), datesmtd(Date[Date]), filter(Table, Table[Flag] ="Reject1"))
Reject2 = calculate(sum(Table[Count]), datesmtd(Date[Date]), filter(Table, Table[Flag] ="Reject1"))
Reject3 = calculate(sum(Table[Count]), datesmtd(Date[Date]), filter(Table, Table[Flag] ="Reject1"))
Incoming = calculate(sum(Table[Count]), datesmtd(Date[Date]), filter(Table, Table[Flag] ="Incoming"))

In matrix use show on row

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[PBI5851]

Hi @amitchandak,

Thank you for the solution. However, this will work only if no other value were to be added to the table. Its my mistake of overlooking that scenario and  not providing that information in my question. If the incoming raw data was in this format where there are two more flags but do not account for the Beginning or End, then the "allselected" will not work. .

DateVal	Count	Flag
1/1/2021	100	Incoming
1/1/2021	-20	Reject1
1/1/2021	-10	Reject2
1/1/2021	-30	Reject3
2/1/2021	-5	Reject1
2/1/2021	-5	Reject2
2/1/2021	-10	Reject3
3/1/2021	20	Incoming
3/1/2021	-30	Reject1
4/1/2021	10	Incoming
1/1/21	30	Contaminated
2/1/21	20	user Error
3/1/21	10	Manufacturing

And the matrix should be

How to achieve this ?