Working with timeline and measures

[Anonymous]

Hi to everyone!

I'm kind off stuck with some measures.

In my model I have two facts tables (SALES and SALES IMS) and one period table (PERIOD). The period table is made with the dates of SALES witch is always up to date. SALES IMS is always two or three months begind.

So, I'm making a report that contains a table visual for showing sales from both tables and a time slicer with the dates in PERIOD. If in the time slicer I select jan-2017 to jun-2017 the measures of SALES will return the sales of that period. In the case of the measures from SALES IMS, they will return sales from jan-2017 to apr-2017, since apr-2017 is the last day with values. So far, this is not a problem. The problem is when I select only jun-2017, the measures from SALES will return data from that specific month, but in the case of SALES IMS will be blank. What I need is measures from SALES IMS return data from the last date with values, in case that there is no data in the selected period. I need the same for previous year sales.

Hope I'm clear.

I will appreciate your help.

Thanks in advance.

Regards.

[v-jiascu-msft]

Hi @Anonymous,

Could you please post a sample with measures here? Maybe you could do it yourself. It's something like this:

Sales IMS =
VAR sales = [your old measure]
RETURN
    IF (
        ISBLANK ( sales ),
        CALCULATE ( [your old measure], SAMEPERIODLASTYEAR ( period[date] ) ),
        sales
    )

The table "period" should contain continuous dates.

Best Regards!

Dale 

[Anonymous]

Hi @v-jiascu-msft

Thanks for your reply and help.

I've tried a measure like that before and didn't work. Anyway, after hours of thinking and work, I was able to work it out. The result is a measure that copied to Word fits in 10 pages. Yes! 10 pages!

The total scenary is so complex that I wouldn't know where to start explaining it.

Thanks for everything.

Have a great day!