Show total amount in current month in column chart.

[Anonymous]

Hi all, 

I hope you can help me out with the following challenge I am trying to solve. 

I have multiple numeric columns that I need to add and would like to show the results in a line and column chart. 

The chart has the months on the x-axis and the total value on y-axis. 

I would like to add the total amount spent with the total amount expected to be spend as a column only on the current month. 

The total amount to be expected however has an unknown month/bookingperiod in the data. I would like to set the date in the measure for the purpose of the visual to the current month, to only show a value of the total in the current month. Same goes for the running total of the total amount spent, also this running total should have a date set this month. 

My dataset works with monthly date intervals. Also, I would still contain the option to zoom in on the data and slice and dice as usual. 

I hope I have explained my challenge well and you can think along with me. 

Thanks in advance! 

Best regards,

Yoran

[v-rzhou-msft]

Hi @Anonymous ,

I think your calculation is based on your data model. Please share me with your pbix file without sensitive data and show me the result you want. This will make it easier for us to find the solution.

Best Regards,

Rico Zhou

[Anonymous]

Hi Amitchandak,

Thanks for your response. 

My dataset looks as follows:

Calendar table

Main dimension table that is used to connect multiple fact tables and to slice and dice in the report

Budget total and spending fact table

Forecast table with calculation done in Excel based on previous dataset

Now I have created a table with the following outcome and set the date of all the data to the beginning of the current month. However, this will create a loop between the calender, forecast and this table and therefore I cannot solve the challenge in this way.

Key_dimensiontable	Spent	Expected to be spent	Expected to be spent2	Date
1	10	5	6	1-5-2022
2	5	20	8	1-5-2022
3	40	10	2	1-5-2022

I would like to show some values of the budget total and spending fact table and set the date to the beginning of the month without altering the actual data in the table itself. I would like to show the amount of spent, expected to be spent and expected to be spent2 in a stacked column chart. So basically I think I need to find a way to create a measure to overwrite the date in the main fact table and set it to the beginning of the month. 

I hope this will make my challenge more clear to you. Thanks for thinking along with me. 

Regards,

Yoran

[amitchandak]

@Anonymous ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.