How to create a table for indivdual months total value in 'Actual/Forecast'

[AstaBr]

Hi,

I have a data table called 'MM Active Financials' for the Projects and its actual and forecasted values. Financial year starts from Aprl - March. I need to recreate a Summary table (snip attached) of Pivot table used before to show all months (From April till current month) with the 'Actual/Forecast' (this column includes Forecasted figures as well which should not be presented in the future months on it own) figures till now. In the data set, there is no date column, only Months names with figures in it that are updated monthly.

The 'Finance Input Type' column has Planned and Actual/Forecast raws. As the values get updated manualy on the monthly basis, we need to create a automated process for current months to appear automaticaly please.

I have 'Total Planned Value' measure created. I do not have pivot data in my loaded data in PowerBI.

The table below is the Totals for September, we are looking at the Total figure in Yellow that needs to be presented for each month from April till now:

I created the 'DateTable' for the date:

Project	Finance Input Type	Total Value	Apr £	May £	Jun £	Jul £	Aug £	Sept £	Oct £	Nov £	Dec £	Jan £	Feb £	Mar £
24/25 - Apixaban Generic	Planned	£                                            110,000	£                                                       -	£                                              10,000	£                                              10,000	£                                              10,000	£                                              10,000	£                                              10,000	£                                              10,000	£                                              10,000	£                                              10,000	£                                              10,000	£                                              10,000	£                                              10,000
24/25 - Apixaban Generic	Actual/Forecast	£                                            273,291	£                                              32,475	£                                              29,412	£                                              27,562	£                                              33,393	£                                              31,440	£                                              29,009	£                                              15,000	£                                              15,000	£                                              15,000	£                                              15,000	£                                              15,000	£                                              15,000
24/25 - BREDI (NR)	Planned	£                                              54,999	£                                                4,583	£                                                4,583	£                                                4,583	£                                                4,583	£                                                4,583	£                                                4,583	£                                                4,583	£                                                4,583	£                                                4,584	£                                                4,584	£                                                4,584	£                                                4,583
24/25 - BREDI (NR)	Actual/Forecast	£                                              69,953	£                                                4,583	£                                                4,583	£                                                4,583	£                                                4,583	£                                              19,537	£                                                4,583	£                                                4,583	£                                                4,583	£                                                4,584	£                                                4,584	£                                                4,584	£                                                4,583

[MFelix]

Hi @AstaBr ,

For this you need to add an additional column to your calendar table that will identify if the value is the current month or previsouly, this will allow you to make a filter at visual, page or report level to only return the data until today.

You can add a conditional column that returns 1 or 0 if the end of month for the date is less or equal to today end of month.

[AstaBr]

Hi,

Thank you for reply, the DateTable is created as a measure, i don’t think i can create conditional formatting on it apart from just a column.
I am not that good with PowerBI and formula creation, would you be able to write all this for me in order for me to be able to execute your answer?