In (matrix) table, how to show only data from last month (available in source) and keep SUM in YTD

[Namoh]

Hi.

I've got a matrix table, with 4 columns:

- targets (per month)

- targets YTD (sum Fiscal Year up till latest date available in actuals)

- actuals (per month)

- actuals YTD (sum FY up till latest date available in actuals)

Instead of showing all months of this FY, I need to show only the figures of the last month, read: the latest month of which Actuls are available.

Currently the latest date Month within Actuals is August.

I've tried to realise it via CurMonthOffset within the Calendar table.

But when I use -1 it showed the latest month until 2 days ago, and now it shows nothing because it looks for data in September (it's now October) while there's only data until August.

If I now use -2 it will show August again, but once I get new data with September (somewhere next week) it will show both September and August Data, which is not what I want.

Besides this, I found another issue, when applying the CurMonthOffset, the YTD values will no longer show the SUM but just the month figure.

I've created a measure "Last Month" which looks up the latest date in my Actuals table (currently 1 August).

If I can create a filter based on this Measure I would solve one issue, unfortunately I can't figue out how to do this.

I don't know if this will also solve the 2nd issue with the YTD SUM not showing correctly.

Any clues how to solve this/these issue/s?

If needed I can share the measure which are used for the columns.

Because of classified data I can't share the pbix unfortunately.

Hope someone can help me.

PS. I'm quite a newbie with PowerBi, so if there are any suggestions, some explanation would be appreciatted. 

[amitchandak]

@Namoh ,

YTD QTY forced=
var _max = maxx('order',[Order date])
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())

Try like these example

YTD QTY forced=
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)

[Namoh]

As stated, I'm pretty new to PowerBi, so with below information related to my measures, should I add a new measure or adjust a current one? And how?

Last time I tried to implement a solution, it took me a whole day, just because I was using the wrong field. Would like to avoid spending a whole day again.

For example, it might be stupid question, but the 'order',[Order Date] part.......don't understand what to put there. 

Measures related to YTD Target

YTD Target Unplanned Downtine =

var _result =

CALCULATE([_4_The first measure],FILTER(ALLSELECTED('CALENDAR'),'CALENDAR'[Date]<=MAX(Targets[Begin van de maand])))

return

IF(

ISBLANK([_4_The first measure]),BLANK(),_result)

_4_The first measure =

VAR _max_date =

MAX ( kpiexport[Month] )

VAR _Total_downtime =

CALCULATE (

SUM(Targets[Target Unplanned downtime (hrs)]),

FILTER (

'Targets',

'Targets'[Begin van de maand] <= _max_date

&& 'Targets'[Begin van de maand] >= DATE ( 2020, 4, 1 )

)

)

RETURN

_Total_downtime

Measures related to YTD Actuals

YTD Actual Unplanned Downtime = CALCULATE([_5_YTD_Actual_Unplanned_Downtime M],USERELATIONSHIP(Targets[Werkeenheid],kpiexport[ShortName]))

_5_YTD_Actual_Unplanned_Downtime M =

var _result =

CALCULATE([_4_The first measure actual],FILTER(ALLSELECTED('CALENDAR'),'CALENDAR'[Date]<=MAX(kpiexport[Month])))

return

IF(

ISBLANK([_4_The first measure actual]),BLANK(),_result)

_4_The first measure actual =

VAR _max_date =

MAX ( 'kpiexport'[Month] )

VAR _Total_downtime =

CALCULATE (

SUM(kpiexport[Un-losses (hrs/month)]),

FILTER (

'kpiexport',

'kpiexport'[Month] <= _max_date

&& 'kpiexport'[Month] >= DATE ( 2020, 4, 1 )

)

)

RETURN

_Total_downtime

[Namoh]

Don't know if I'm going the right path, but I've adjusted the first part, but at the second part I get an error stating: Unexpected expression 'DATESYTD'

[Namoh]

Any clues where I'm going wrong?

I want to show only the latest month of which we've actual data, and this should update when data from a new month is added to the source, without having to manually adjust filters or something else, it should be automatically.

[BI_Jo]

Hi

I found this video tutorial which sounds like it could help with your query:

https://www.youtube.com/watch?v=HBFc1eBSfb8

Quite easy to follow along to.

[Namoh]

Thanks, I looked at the video, and tried to use it on my data.

Problem I'm facing is that the lookup value (as used in the video) in my case is already a measure, and I get an error that it can't be used in this expression.

I've got target and actual data.
Target data has months until end of the FY.

Actual data has new data every month, so after refreshing a new month is/comes available.

I've already created a measure to lookup the latest month in the actuals data.
But using this measure in the expression as shown in the video gives me an error.

And I already can create a table with target and actual data in it, but that shows all months (incl the YTD SUM).

I just want to show the data of the latest month.

[v-lionel-msft]

Hi @Namoh ,

Do you mind sharing your .pbix file or sample data?

Although your description is very detailed, we still cannot determine the problem without seeing the data model.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Namoh]

Unfortunatly because of classified data I'm not allowed to share my pbix file.

What information do you need?

I might be able to provide the information needed with altered printscreens and/or my used formulas / measures.

Basically what I want to do (without looking at my current issue) is showing my Month and FY YTD (for that month) values for both Target and Actual data but only showing the latest month where Actuals are available. Based on the values of the Actuals compared to the Targets the Actual figures need to get a color (now done with Conditional Formatting.

Picture 1, shows what I want to build (now done in excel).

Picture 2, shows random data for 1 facility.

In this case I would like to only see 1 row (for August) with the 4 columns: Target (month), Target YTD (for that month), Actual (month), Actual YTD (for that month).

Facility	Target (month)	Target YTD (for that month)	Actual (month)	Actual YTD (for that month
Fac 1	9	74	12	80

and when next months data (September) is added to the source, I would like to have the table automatically show only the September data.

Facility	Target (month)	Target YTD (for that month)	Actual (month)	Actual YTD (for that month
Fac 1	15	89	13	93

Currently I've tried this to do with measures (with help of this community, much appreciatted) and I have the YTD sums (targets and actuals) working (summing from Apr 20).

The issue is that I can't get to just show August while having the YTD sum still correct.

The YTD Target figure used in the table is calculated as follows:

YTD Target Planned Downtine =

var _result =

CALCULATE([The first measure planned],FILTER(ALLSELECTED('CALENDAR'),'CALENDAR'[Date]<=MAX(Targets[Begin van de maand])))

return

IF(

    ISBLANK([The first measure planned]),BLANK(),_result)

Where the "The first measure planned" is calculated as follows:

The first measure planned =

VAR _max_date =

    MAX ( kpiexport[Month] )

VAR _Total_downtime =

    CALCULATE (

        SUM(Targets[Target Planned downtime (hrs)]),

        FILTER (

            'Targets',

            'Targets'[Begin van de maand] <= _max_date

            && 'Targets'[Begin van de maand] >= DATE ( 2020, 4, 1 )

                        )

    )

RETURN

_Total_downtime

The Actual YTD is calculated as follows (because the facilities in both tables aren't linked a userrelationship had to be used):

YTD Actual Planned Downtime = CALCULATE([_5_YTD_Actual_Planned_Downtime M],USERELATIONSHIP(Targets[Werkeenheid],kpiexport[ShortName]))

Where "_5_YTD_Actual_Planned_Downtime M" is calculates as follows:

_5_YTD_Actual_Planned_Downtime M =

var _result =

CALCULATE([_4_The first measure actual planned],FILTER(ALLSELECTED('CALENDAR'),'CALENDAR'[Date]<=MAX(kpiexport[Month])))

return

IF(

    ISBLANK([_4_The first measure actual planned]),BLANK(),_result)

Where "_4_The first measrue actual planned" is calculated as follows:

_4_The first measure actual planned =

VAR _max_date =

    MAX ( 'kpiexport'[Month] )

VAR _Total_downtime =

    CALCULATE (

        SUM(kpiexport[Pl-losses (hrs/month)]),

        FILTER (

            'kpiexport',

            'kpiexport'[Month] <= _max_date

            && 'kpiexport'[Month] >= DATE ( 2020, 4, 1 )

                        )

    )

RETURN

_Total_downtime

To be honest, I don't know if what I want is even possible with using measures.

If not, I need a PQE/M formula that can calculate per row the YTD values.

I think this would be the easiest way, but I don't know if there's a function where you can do a sum on row level as you can do in excel, like in below picture column C and G.

I know I posted a lot of information, but hopefully this helps to get the solution.

If needed, a teams call where I can show my data/pbix file would also be a possibility.