Forum Discussion
Beginning and Ending value
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.
3 Replies
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.
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 ?
Please could you send me a pbix file using this solution. It doesnt work when i try it out.
