Forum Discussion

AsafPinhas's avatar
AsafPinhas
Regular Visitor
3 years ago
Solved

How to Show data up to a certain Month?

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 ...
Need Help
  • AsafPinhas's avatar
    AsafPinhas
    3 years ago

    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.
     
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
AsafPinhas
Regular Visitor
3 years ago

Hi Anonymous ,

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