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

     

     

Anonymous's avatar
Anonymous
Not applicable
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