Column Values Based on Selected Slicer - Cumulative and Specific

[TheSAY]

Hello,

I am trying to figure out how to go about changing a report I have created to allow cumulative and specific selected values to show.

The report is called Monthly FTE which provides information for enrolled children in our childcare program.  Based on a "Month" slicer, we're able to get data for the selected month.  Here's an example of what shows:

JANUARY - Number "1" is selected in the slicer.

FEBRUARY - Number "2" is selected in the slicer.

When we choose both "1" and "2" for January and February, this is what happens:

What we need to have happen, if "1" and "2" for January and February are selected, is:

1. Budget FTE should show the latest selected option.

2. Actual FTE should show the latest selected option.

3. Total Children Enrolled by Program should show the lastest selected option.

4. Total Revenue should be cumulative.  This is already happening.

5. 2019 Budget should be cumulative.  This is already happening.

6. % Rev should be cumulative.  The % has the following equation (Revenue / Budget).  It's just summing the two selected months together, rather than using the appropriate equation.

In the above example, if "1" and "2" for January and February were selected, it should look like:

BH Before Care (40)        | 40.0 | 34.2 | 37 | $14,145.00 | $14,830.00 | 95%

BH Extended Hours (20) | 21.0 | 18.6 | 19 |   $2,598.00 |   $2,755.00 | 94%

AND SO ON

Here is the PBIX file as well as January's and February's spreadsheets: https://www.dropbox.com/s/tso4klsuw42dmez/MonthlyFTE.zip?dl=0

If anyone can help, I would be most appreciative.

[Ashish_Mathur]

Hi,

In the absense of a Calendar Table, using Date and Time intelligence functions becomes difficult.  Those functions are required for computing YTD/cumulative figures.  To create a Calendar Table, we must first have a date column in the FTE Budget and Finances datasets.  Even if those datasets do not have a date column but only a Month and Year, we can always build a Date column and create a relationship from the Date column of those datasets to the Date column of the Calendar Table.  There is a lot of work to be done before your question can get answered.

[Greg_Deckler]

There's a lot going on there. See if my Time Intelligence the Hard Way provides some assistance for the cumulative stuff.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

For showing the most current selected month, generally you use a measure that looks something along the lines of:

Measure = 
VAR __maxMonth = MAX('Table'[Month])
RETURN
SUMX(FILTER(ALL('Table'),[Month] = __maxMonth),[Value])

The first part gets the max value from your slicer and then uses that to filter down to those rows in your fact table. Your ALL overrides any context in the visual. You will likely have to have a disconnected slicer or table used as your slicer for this to work the way you want. This may cause problems with the cumulative values that you will need to correct.