How to Show data up to a certain Month?

[AsafPinhas]

Hi, 

i have a transactional data of my share in hospitals, it contains the following columns:
[Month] - the month of the report
[Hospital Code] - identifier unique for each hospital on my list

[Total Births] - how many babies were born in month

[Total Babies Treated] - how many babies were treated with my drug/the equivalent treatment by competitors

[Total Babies Treated by my Drug] - how many babies were treated with my drug

problem is i'm not getting the data on a regular basis and have gaps between the different hospitals. 

I have additional table with forecasts for the entire year structured the same way and a dimension holding the hospital data.

my problem is: i want to be able to compare my actual vs forecast share for all hospitals that reported data.

if out of 20 hospital i have 10 that reported Jan-Jun data i want the table to show only these 10 hospitals

and if i have additional 3 that reported Jan-Apr i want the table to show 13 hospital with data only up to Apr (excluding June).

I tired using virtual table using this DAX:

var DD = MAX(Dim_Hospitals[LastUpdate])

var ShareTable =

    CALCULATETABLE(Fact_HospitalShareForecast, FILTER(Fact_HospitalShareForecast, Fact_HospitalShareForecast[LastUpdate]>=DD))

but when i get it into a Matrix it doesn't give me the desired outcome.

[AsafPinhas · Accepted solution]

Hi, 

so found a nice solution to this issue:

1. created a single column table containing only the Last Update date for each hospital not connected to any other table in the model

2. building a single-select filter based on this table 

3. creating a virtual table within a measure:

VAR LastUpDate = SELECTEDVALUE(Dim_LastDate[Last Update])

VAR Hospitable =

    CALCULATETABLE(Fact_HospitalShare, FILTER((Fact_HospitalShare), Fact_HospitalShare[LastUpdate]>= LastUpDate), FILTER(Fact_HospitalShare, Fact_HospitalShare[Date]<=LastUpDate))

Return

SUMX(Hospitable, [Total Usage])

this way the virtual table contains only data for hospitals reported up to or later than the selected date and for these hospitals it contains only the data up to the selected date.

[v-stephen-msft]

Hi @AsafPinhas ,

Have you tried to create a calendar table as a filter for dates? The date column of the calendar table creates a relationship with the dates in your two tables. When you filter the date column of the calendar table as a slicer, you can filter the data from both tables. The date range is the range of dates in your slicer.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[AsafPinhas]

Hi @v-stephen-msft ,

I have a date dimenstion which both transactional data tables are connected to.
when I filter by date I still see all 20 hospital with blank data points for the relevant months.

let's say for June only 4 hospitals reported their data, i'd like to compare actual vs. forecast only for those hospitals without having to manually filter these four. 

Regards, 

Asaf