Hi All!
I've come as a sort of cry for help. I'm relatively new to PowerBi and all the complexities that come with DAX and measures. So I would appreciate some clarity on this problem I'm having.
DATA GIVEN:
The data I am working with is a mix of sources and measures. I have country names and information on population numbers 'with coverage' for a range of years 2002-2019. Measures are used to calculate those without coverage and any other relevant stats (%, multipliers)
GOAL:
The following table is a table I am trying to replicate using measures (i.e avoiding having to manually create a dataset looking like this for more years) and inputs from the user in the form of a range of years (i.e. comparing 2004-2017 would mean looking at only 2004 and 2017, what is in between does not matter). DW stands for drinking water and the datset is demonstrating the coverage in # of people. The data set is larger than this and has data relating to years spanning 2002-2019. There are also more calculations involved later down the line. For now, I am attempting a basic replication of 2018-2019.
CURRENT PROCESS:
The above image shows what the filter range looks like with two measures calculating the MIN or MAX of the range input. This functions (as you can see from the table on the far right). I use the following:
YearMin = CALCULATE(MIN('DATA'[Year])) (Or the STARTING YEAR 2018)
YearMax = CALCULATE(MAX('DATA'[Year])) (Or the ENDING YEAR 2019)
Now, the next step or train of though would be to use this measure value to filter the larger prexisting data columns or measures (note: the 'coverage with' is a given source and the 'coverage w/out' is a separately calculated measure using total population data provided). So when implmenting the following measures:
Start Pop w Basic Drinking Water = CALCULATE(SUM(DATA[Pop with Basic+ Drinking Water]), FILTER('DATA',[Year]= [YearMin]))
Start Pop w/out Basic Drinking Water = CALCULATE(SUMX('DATA', [Pop w/out Basic Drinking Water]),FILTER('DATA', CALCULATE(MIN(DATA[Year]))) ) (here I tried a different method since the above didnt work either, both dont work anyways)
End Pop w Basic Drinking Water = CALCULATE(SUM(DATA[Pop with Basic+ Drinking Water]), FILTER('DATA', [Year]= [YearMax]))
End Pop w/out Basic Drinking Water = CALCULATE(SUMX(DATA, [Pop w/out Basic Drinking Water]),FILTER('DATA', CALCULATE(MAX([Year])) ) )
I get the following table
It just does a summation of both years or starting/ending (in the measures as min/max).
I cannot simply reduce the measures to be min or max functions since some information shows lower values in the ending/max years (i.e Tanzania, highlighted yellow).
I am a bit stumped. I can do the problem for sure manually, if I am looking at only two years. However, my team is considering the usefulness of cross comparing multiple ranges. So doing it manually for that would be very very tedious. Anyways, I have lots of learning to do but this project is taking up way too much of my time to say I've been doing it in a productive manner.
Any help would be appreciated!!! (I can provide the .pbix and excel files if needed)
