Forum Discussion

BSawd's avatar
BSawd
Frequent Visitor
5 years ago
Solved

Return months where data is complete, with access controls

Hi all   I have a report designed which pulls together data from 4 NHS Prescribing Data sites (England, Wales, Scotland, NI) into one appended table with a column for country (sample below).  M...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi BSawd ,

     

    I did the following steps

     

    1. Created a table  CountryLastDate which has Country and LastDate of sales as columns.

        This you can create from Sales Fact Table.

    2. Created a Relationship between this and Geography on Country column

    3. Created Measure

       

    RevnueBYCountry =
    VAR _SelMinDate =
    MINX ( ALLSELECTED ( Geography ), RELATED ( CountryLastDate[LAstDate] ) )
    RETURN
    CALCULATE (
    SUM ( Sales[Revenue] ),
    FILTER ( ( 'Date' ), 'Date'[Date] <= _SelMinDate )
    )
     
    4.  Created a matrix  visual to display the measure.
     
    I am unable to add the pbix file here. I will try to add as apersonal message. Take a look.
     
    If it solves your problem please accpet this as a solution and also give kudos.
     
    Please share your pbix if you need further help.
     
    Cheers
     
    CheenuSing

     

     

BSawd's avatar
BSawd
Frequent Visitor
5 years ago

Thank you so much! This works perfectly, really appreciate the quick response. I'll mark this as the solution.

 

Only one nice to have: at the moment if I view the report as a user who only has access to certain countries, the data is still filtering to a date defined by the last availability of another country, even if that user doesn't have any data from that country. So for example, one user's regions only cover England and data is available to April for that country but unless I actively filter for England it's limiting data to Feb because that is the max date for Scotland. Not a huge issue, as it just means they need to click a slicer to get the extra month, but if it were an easy fix that would be great to have.

 

(apologies if this is duplicated, I tried to reply and it didn't seem to do anything)