Blank when trying to show only data up to the end of last month

[Anonymous]

Hi guys,

I'm trying to show a graph with the actuals of 2020 (so, so far it's only Jan, Feb 2020), the budget (2020) and Same period last year (2019). 

Everything is coming through fine but I have March 2020 showing too (because it's been a few days of March).

I tried to use this formula - it's worked for another dataset but it doesn't work for this one and I can't think of why this is happening besides the fact that maybe the dataset it worked on had it by days (i.e 1 Jan 2020, 2 Jan 2020, 3 Jan 2020, etc.) and this dataset that i'm trying to work on only has it by Jan 2020, Feb 2020 (Periods and not dates). 

Test = IF ( MAX('Calendar Table'[Date]) <= MAX ( Premium[Date] ) , SUM(PL_Premium[GWP] ), BLANK() ) 

I created a calculated column for Premium[Date]:

Date = DATE( LEFT(PL_Premium[Period],4) , RIGHT(PL_Premium[Period],2) , 01 )

where Period = 201901, 201902, 201903, etc. 

It just comes up as blank when I used that formula - any idea why? 

[Ashish_Mathur]

Hi,

Here's what i would do.

1. Convert the period column to the first date of the month
2. Create a Calendar Table and extract Year and Month via the following calculated column formulas: Year = Year(Calendar[Date), Month Name = FORMAT(Calendar[Date],"mmmm"), Month number = MONTH(Calendar[Date]).  Sort the Month Name column by the Month number column
3. Create a relationship from the Date column of the PL_premium Table to the Date column of the Calendar Table
4. To your visual, drag the Year and Month from the Calendar Table
5. Write these measures

Premium collected = SUM(PL_Premium[GWP])

Premium collected YTD = CALCULATE([Premium collected],DATESBETWEEN(Calendar[Date],DATE(YEAR(TODAY()),1,1),EOMONTH(TODAY(),-1)))

Premium collected same period last year YTD = CALCULATE([Premium collected],DATESBETWEEN(Calendar[Date],DATE(YEAR(TODAY())-1,1,1),EDATE(EOMONTH(TODAY(),-1),-12))

Hope this helps.

[Anonymous]

Thanks for your response @Ashish_Mathur , 

I've followed the steps - I've already done steps 1 - 4 essentially (see response to v-lionel)

I tried your formula but it's still giving me March figures (Since some March transactions have happened already). I'm only wanting Jan - Feb and I'm pretty sure the formula is working because it worked for another dataset with contiguous dates, but PL_Premium is using Period (202003, 202002, etc) which I think might be the issue? Have you had this issue before. Formula below: 

Test = IF ( MAX('Calendar Table'[Date]) <= max( PL_Premium[Date] ) , SUM(PL_Premium[GWP]), BLANK() ) 

[Ashish_Mathur]

Hi,

I can help further only when i get the PBI file.

[v-lionel-msft]

Hi @Anonymous ,

The calculated column [Date] can work well.

Errors in this [Test] measure may have the following reasons:

1. Data model error.

Try to create relationship between Premium table and Calendar Table.

2. Visualization errors

You may need to add [date] of Calendar Table to the visual.

3. Formula errors.

Please give me a sample data model of Premium table, 'Calendar Table', PL_Premium table.

Best regards,
Lionel Chen

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

[Anonymous]

Hi @v-lionel-msft ,

Thanks for your response.

1. I have linked the two together already via relationships. 

2. I used 

Month = FORMAT('Calendar Table'[Date],"MMM-YY") as my shared axis on my graph. 

3. I've given the formulae in the initial post - this formula isn't linking to anything else. 

How do I give a sample set of data? 

Apologies - when I say Premium Table in my first post, it was PL_Premium but I thought I'd simplify the table so it was easier to read. 

Premium Table essentially looks like this (the 3 main columns I'm using for this formula): 

Product	Period	Premium
X	202003	1,000
X	202003	400
Y	202001	500
Z	201912	300
Z	201911	100

With the calculated column (Date = DATE( LEFT(PL_Premium[Period],4) , RIGHT(PL_Premium[Period],2) , 1 )) which is what the calendar table date is linking to in the relationships. 

and for the Calendar Table:

Calendar Table = CALENDAR(DATE (2018,01,01), DATE(2020,12,31))

Also, do you think it could be because it's direct query? I don't think so, but I have a lot of datasets in this report and I don't want to change it from DQ to Import just to try it. 

I think it's because it's working off a period (202003) instead of an actual date (2020/03/01) for example, and since the current date column is a calculated column noted above, that might be the issue?!