Power Pivot - DAX Measure daily history missing quantity

[JanusG]

Hi there,

since I am in practice mode, I have put together a virtual company (in a workbook) for myself. The first hurdle is as follows:

Using the table, you can see the month of February and the daily transport quantity. I have set the minimum quantity to 5,000,000. Now I would like to calculate the column (codice by peso) so that the missing quantity is reduced with each day on which transports took place. That is, on the first day it starts with 5,000,000 and then becomes less and less.

As you can see, I have created a measure that correctly displays the missing quantity in the "Grand Total", but not the result of the current subtraction. The 5,000,000 are hardcoded and not available as a table / column reference.

I have already tried a few things, but always end up with this kind of display, even with different measures.

The Star Schema looks like this.

Now my question to the community, which measure do I have to create so that the representation I want takes place? If someone would be so kind as to write me the solution, I would also be grateful for a brief explanation. I want learn and understand.

Thanks in advance

Janus

[tamerj1]

Hi @JanusG 

please try

Peso bu codice =
VAR SelectedDates =
ALLSELECTED ( 'Calendar'[Date] )
VAR CurrentDate =
MAX ( 'Calendar'[Date] )
VAR DatesOnAndBefore =
FILTER ( SelectedDates, 'Calendar'[Date] <= CurrentDate )
RETURN
5000000 - SUMX ( DatesOnAndBefore, [SUMX Peso] )

[JanusG]

Hello @tamerj1,

I have attached a screenshot of your proposal. January and February together. Okay, I put the subtraction of the target at the end of the measure so that the result is negative. The numbers in red result from my measure.

If you look closely at BOTH screenshots, you will notice that your measure only takes the monthly target of 5,000,000 into account at the beginning of the calculation and then continues calculating. In February it starts with the positive result from January.

[tamerj1]

Hi @JanusG 
Please try

Peso bu codice =
VAR SelectedDates =
    CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), ALL ( 'Calendar'[Date] ) )
VAR CurrentDate =
    MAX ( 'Calendar'[Date] )
VAR DatesOnAndBefore =
    FILTER ( SelectedDates, 'Calendar'[Date] <= CurrentDate )
RETURN
    5000000 - SUMX ( DatesOnAndBefore, [SUMX Peso] )

[JanusG]

... what can I write, with monthly evaluation it now works.
If there wasn't a but. Take a look at the screenshot.
I have activated January and February using Slicer.

[tamerj1]

@JanusG 

I'm sorry I didn't get you point. Would you please clarify further. 

[JanusG]

No problem, with pleasure. The minimum transport volume must be 5000000 kg per every month. Ergo, the counter is reset to 5.000000 on the 01st of each month.

Your measure correctly takes into account a monthly evaluation! However, if I select two months via the slicer, the minimum volume is only taken into account once. I tried to show this with the last screenshot.

[tamerj1 · Accepted solution]

@JanusG 

Peso bu codice =
VAR SelectedDates =
CALCULATETABLE (
VALUES ( 'Calendar'[Date] ),
ALL ( 'Calendar'[Date] ),
VALUES ( 'Calendar'[Month] )
)
VAR CurrentDate =
MAX ( 'Calendar'[Date] )
VAR DatesOnAndBefore =
FILTER ( SelectedDates, 'Calendar'[Date] <= CurrentDate )
RETURN
5000000 - SUMX ( DatesOnAndBefore, [SUMX Peso] )

[JanusG]

... and the winner is? Yes, you did it!

Before I accept the solution, I would like to know what the difference is between your solution and mine.

[tamerj1]

@JanusG 
You need to minus the sum of all [SUMX Peso] before the date in the current filter context.

[Adamboer]

To calculate the column (codice by peso) so that the missing quantity is reduced with each day on which transports took place, you can create a new measure using DAX formula. In the formula, you can subtract the cumulative sum of transport quantity till that date from the minimum quantity of 5,000,000.

The formula for the measure would be:

codice by peso = 5000000 - CALCULATE(SUM(transport quantity), FILTER('table', 'table'[month]=EARLIER('table'[month]) && 'table'[day]<=EARLIER('table'[day])))

This formula will calculate the cumulative sum of the transport quantity for each day in February and subtract it from the minimum quantity of 5,000,000 to give the result of the current subtraction in the column (codice by peso).

Hope this helps!

[JanusG]

Thanks, but I have problem to understand what you mean with

"FILTER('table', 'table'[month]=EARLIER('table'[month]) && 'table'[day]<=EARLIER('table'[day]))"

For me there is only one table (calendar) with month and day and then it don't function. Or am I understanding something completely wrong?

[Adamboer]

Apologies for any confusion caused. Here is an explanation of the formula I provided:

The formula I provided is using the FILTER function to filter the rows in the 'table' that match the following conditions:

• The 'month' column value is equal to the 'month' column value of the current row being evaluated (EARLIER('table'[month])).
• The 'day' column value is less than or equal to the 'day' column value of the current row being evaluated (EARLIER('table'[day])).

This is to ensure that only the rows with a 'month' value equal to or earlier than the current row being evaluated, and a 'day' value equal to or earlier than the current row being evaluated, are included in the calculation.

In your case, if you only have one table with 'month' and 'day' columns, then you can modify the formula as follows:

FILTER('table', 'table'[month]=EARLIER('table'[month]) && 'table'[day]<=EARLIER('table'[day]))

Replace 'table' with the name of your table.

I hope this helps. Let me know if you have any further questions or concerns.

[JanusG]

I have tried to reproduce your measure. However, I got the error message:

>>Calculation error in measure 'dRASA'[Test Mancanza]: EARLIER/EARLIEST refers to an earlier row context which doesn't exist.<<

This error message also makes sense to me. I have attached a screenshot showing the measure.

[JanusG]

Thanks, I will go through your explanation again with the suggestion and answer you.

For now, I have found a (partial) solution.

With the following measure, I get the result that is shown in the screenshot. Okay, I didn't mention that I select the respective month via a slicer. I'm just thinking if I should hide the days without transport by measure.

The Measure is:

=VAR _BasePeso=5000000

RETURN
TOTALMTD([SUMX Peso];'Calendar'[Date];dRASA[Data])-_BasePeso

[wdx223_Daniel]

codice by peso=VAR _BasePeso=5000000 RETURN _BasePeso-CALCULATE([SUMX Peso],'CalendarTable'[Date]<=MAX('CalendarTable'[Date]))

[JanusG]

Thanks, but unfortunately, this formula does not work.

The following error message:

>>This formula is invalid or incomplete: 'Calculation error in measure 'dRASA'[Mancanza per Mese]: A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.'.<<