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..