Customer gallons - Gain, Loss and net

[BudMan512]

Hi All,

I have a report with three pages, gallon gains, losses and net.  Here are the Gain and Loss pages.  I have seperate slicers for Start Date and End Date.

I am having problems calculating the Net Gallons as the  two slicers are not rendering any results.

I need a solution for two calculations as seen below on the Net page.  

Net Est Gal = Est Gallons - Volume Lost TTM (Trailing 12 months)

Net Actual Gal = Total 12 Actual Gal Gained - Volum Lost TTM

I have included a link to the report below.

I would appreciate any help.

Thanks,

Bud

https://1drv.ms/u/s!An-c-kQsqoNPgnZb67wKvSw7M4OQ?e=mjU67O

[some_bih]

Hi @BudMan512 check link for sync visuals / slicer (as you use same slicer for dates on different pages)

[BudMan512]

@some_bih

I was able to sync the two slicers together using the Sync slicers advanced options, which is handy.

However, this does not resolve my problem.

Both calculations for net gallons  contain elements with start and end dates.  So when Start and End dates are for the same period they filter each other out.  Or said another way, you don't normally have customers who start and end in the same month.  Is there a way to handle these calculations that works? Below shows the filtering problems I am having with the Cards for each calculation.

This is a one minute calculation in Excel but not so easy in PBI. I appreciate any help I can get.

Thanks,

Bud

[some_bih]

Hi @BudMan512 my "issue" is gallons (I am from Europe), liters or similar is closer to me 🙂

If you need to filter something for period, like

higher than something (some column)  AND 

Lower than something (some column or another one)  simply use FILTER function (if it is in the same table)

[some_bih]

Hi @BudMan512 

In measure 

Volume Lost TTM = CALCULATE(

SUM(Transactions[Qty]),

--USERELATIONSHIP(Customer[END_DATE],'Date'[Date]),

FILTER(

ALLSELECTED('Calendar'),

    'Calendar'[Date]

<=SELECTEDVALUE(

    'Customer'[End Date])

&&'Calendar'[Date]>=

EDATE(

SELECTEDVALUE(

    'Customer'[End Date]),-12)

)

)

ALLSELECTED is used in definition.

Still, in another measure you use this measure with ALLSELECTED

Total Lost Gallons = SUMX(VALUES(Customer[End Date]),[Volume Lost TTM])

This is not best practice as ALLSELECTED should be used only in visual as results are impossible to intepret.

Try to rewrite Volume Lost TTM measure without ALLSELECTED

[BudMan512]

Hi @some_bih

Thank you for your recent post and your suggestions to try.

I have spent many hours attempting changes  in the DAX but to no avail.

I wonder if you could provide more detailed assistance or if not perhaps you know of someone else who could help me with this.

I appreciate your time.

Thanks,

Bud

[some_bih]

Hi @BudMan512 I am happy if I could assist you.

Maybe to start from scratch: what you want to calculate, show, at which grain, per customer...what is ecpected output for some sample input. Let' s go step by step

[BudMan512]

@some_bih

Ok, starting from the beginnig.

I have a 3 page report.   Page 1 is the volume of gas gained, Page 2 is the volume of gas lost and Page 3 is the Net volume which is equal to volume gained - volume lost.

Page 1 Gains

This page  contains a table of Account, Start Date, Tank Capacity, Estimated volume gained and 12 month actual volume gained.

Estimated volume gained = Tank Capacity * .8

Total 12 Month Actual Volume Gained (measure) = the acual volume delivered in 12 month period following the Start Date.

There is also a slicer on this page to filter the date range 

There are two cards included:

first is a card with Sum of Est Volume Gained

and also a card with Total 12 Month Actual Volume Gained.

12 month actual volume = CALCULATE(

SUM('Transactions'[Qty]),

FILTER(

ALLSELECTED('Calendar'),

    'Calendar'[Date]  

>=SELECTEDVALUE(  

    'Customer'[Start Date])  

&&'Calendar'[Date]<=  

EDATE(  

SELECTEDVALUE(

    'Customer'[Start Date]),12)))  

Total 12 Month Actual Volume Gained = SUMX(VALUES(Customer[Start Date]),[12 month actual volume])

Page 2 Loss

Contains a table with Account, End Date, Category and Volume lost TTM (trailing 12 months)

There is also a slicer with End Date

and a Card with Total Lost Volume

Volume Lost TTM = CALCULATE(

SUM(Transactions[Qty]),

FILTER(

ALLSELECTED('Calendar'),

    'Calendar'[Date]

<=SELECTEDVALUE(

    'Customer'[End Date])

&&'Calendar'[Date]>=

EDATE(

SELECTEDVALUE(

    'Customer'[End Date]),-12)

)

)

Total Lost Volume = SUMX(VALUES(Customer[End Date]),[Volume Lost TTM])

Page 3 Net

There are two text boxes with general descriptions of the formulas needed.

Net Est Volume = Est Volume Gained - Total Lost Volume

Net Actual Volume = Total 12 Actual Volume Gained - Total Lost Volume

Below there are two cards showing Blank as a result.  This is what I need help with.

Sum of Net Est Volume

Sum of Net Actual Volume.

These are both returning blanks.  

There are also two date slicers on this page, one for Start Date and one for End Date.

 Net Est Volume = Customer[Est volume Gained] - [Volume Lost TTM]

Net Actual Volume = [Total 12 Month Actual Volume Gained] - [Volume Lost TTM]

Link to report is at the bottom

Here is the model.  

Gain-loss-net for forum v3.pbix

[some_bih]

Hi @BudMan512 ok, I will check it and let you know status 

[some_bih]

Hi @BudMan512 

as I checked:

- visuals on Canvas Net provide some amount when you change Start and End Date- dare these results ok for you?

Inputs:

Start Date and End Date = 01.01.2020 (start)

Start Date and End Date = 05.03.2024  (end)

- if you put measures in Customer table in separate columns (similar to your formulas) for debugging purpose, it seems that EndDate column when is Blank create possible issues? Blank values are ok? How it affect calculations? Should't it be some non-blank date, like transaction date or ...

- did you think about "moving" Start Date and End Date from Customer Table into Transaction table (still think about blank End Date) as Calendar Date is connected to Transactions?

This way it could be option to easly calculate data for date between period. This should stand for other Customer table columns

[BudMan512]

Hi @some_bih 

Thanks for your reply.

I tried using the date range you suggested and although they did produce some results, they were not accurate.

Not every customer will have an end date (date no longer a customer) as if they did the company would have no active customers.  So yes, blank values are ok. 

I tried moving Start Date and End Date to Transactions but it broke all my visuals and would not allow me to use a date range slicer.  I didn't think this was a good solution so I discontinued working on that end of it. 

I may need a date table for start and end dates but have not had much luck sharing the date table.

Net gallons measures contain start date and end date elements and as a result it looks like the start and end slicers appear to be eliminating any results.  The only exception would be  if a customer were gained and lost during the specified date range.

I didn't full understand all of what you were trying to say, so I'm sorry if I missed an important point.

Thanks for your time.

Bud

[some_bih · Accepted solution]

Hi @BudMan512  is some new version of file I moved Start and End date per Account into Transaction table (this make sense as it seems your calculation need only active customers (with non-blank end date))

Two new columns in Transaction table (no blanks in CustomerStartDate for CustomerEndDate with IF you can add some dates).

This way it could be possible to filter for period you want quantity.

Let me know what you think.

CustomerStartDate =

CALCULATE(MAX(Customer[Start Date]),

FILTER(Customer, Customer[Account]=Transactions[Account])

)

CustomerEndDate =

    CALCULATE(MAX(Customer[End Date]),

    FILTER(Customer, Customer[Account]=Transactions[Account])

    )

[BudMan512]

@some_bih

Hey,

I added the two date columns to the transaction table and then substituted Transacttions[CustomerStartDate] and Transactions[CustomerEndDate] for Customer[Start_Date] and Customer[End_Date] in my Measures and Calculated Columns and now the report works perfectly. Thanks for sticking with it, much appreciated.

Bud

[some_bih]

Hi @BudMan512 I'm really glad it worked. 😊

[BudMan512]

@some_bih

Thanks for your response.  I will try what you have suggested and get back to you.

I got pulled off this project but will get back to it soon.  Much appreciated. Bud

[BudMan512]

Sounds good. I am off until Monday so will get back in touch then.  thanks