Exclude data if total for a column for a specific period is negative ?

[Anonymous]

Hi all,

I have an Excel data file which contains sales volumes and already has all the periods of 2019 ready to be refreshed. Based on when the reporting has been done, this data is refreshed from a reporting program. So for 10/2019 I will now have an error message because the reporting period is not open or the values will be 0. But that is ok.

When the period is open but nothing has been reported (09/2019 right now) my data will be negative for all categories and numbers. This is when I would want to have a query or dax formula which evaluates if the total volume for the period is negative, the data for the period should be excluded.

This is periodic data so sometimes it can happen that a negative value appears in a reported month. So I can not just create a measure that says "if volume <0 then 0" for example. Because for some months there will actually be a handful of negative values. 

It's only when the total volumes of a period is negative that I would want the data to be excluded.

Anyone have an idea? Attached you can find an example of the data in loaded in the query. (period is linked to the datekey in the date table)

[Nathaniel_C]

Hi @Anonymous ,

So where would you be using this data? If the sum of the data for the current month is negative, then exclude the data for the month? As if this period is not even open. Date greater than the end of the previous period not reported?

Think we need a little more info.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

Hi @Nathaniel_C 

The data comes from an Excel file which gets data from a refresh based on "Oracle Hyperion". The data reported is YTD.

The data that I am refreshing is periodic. This periodic data is refreshed for and loaded to the data model.

I want the user to only refresh and not having to change any period,.. in the Excel file. So for 10/2019 I can already see "No access" for figures as that period is not yet open.

Because we report on YTD data, and I download periodic data, this means that when the period for 09/2019 is open but nothing has yet been reported (which is now) the periodic values for 09/2019 will be in minus. Because YTD the amount is 0. And periodic the amount goes from "1000" in 08/2019 to 0 in 09/2019 => Which means -1000 in periodic values.

So somehow I would need to eliminate the data for which the total sum of a period is negative. In the query directly or with a measure for my visuals.  But I do not know if that is possible within query-editor, and I don't know that much about DAX yet to write my own function for "the total of a certain period".

There can be negative amounts in some months (for example a correction going from 1 to 0 next month means -1 in periodic data) so I can't evaluate it on a row basis in a measure by just saying "if(sum([column]) = <0 then 0". It needs to always look at the total sum for the period and can not be evaluated on a row level.

But no idea how to make a measure based on a total of a column always? Or how to state this in the query editor?

You could say that the user should only refresh after reporting has been done, but I would like to foresee that the user can refresh at any time without having to change anything in the visuals/model..

Any idea?