Show data of months of running year but only up to current month

[Dev13]

Hello,

I have a single table with the following columns:

YEAR, MONTH, ACTUAL, TARGET.

I would like to show in a line chart the ratio of ACTUAL/TARGET (which I have already calculated) for every month of the running year but only for the months up until/before the running month.

My dataset has monthly data for 2019 and 2020. I would like to show in the line chart the months 1 to 10 (besause November is not over yet) for the current year (2020). So my line chart will show data for 10 months.

In February 2021 it will only show a point in the chart since there will only be complete data for January of the year 2021.

In March 2021 it will show data only for 2 months, In April 2021 for 3 months etc...

How is this possible?   

[edhans]

You would need a Date table to do this properly, and have a field in the date table that evaluates to true/false for furture months. You could have November be "False" until December 1, at which point it becomes true. Then use some Time Intelligence using SAMEPERIODLASTYEAR for your data.

I'd need to see data to really help though, but that is the concept. Date tables are critical for Power BI to work efficiently and realtively easily when you need to filter and calculate based on dates.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Dev13]

Hi @edhans and thank you for the reply.

I already have a date table but it has a relationship with my fact table already.

This table with the ACTUAL and TARGET that I'm talking about is kind of a second fact table. It is just there to show the specific info it has without interracting with the rest of the star-schema.

I think I will add a second date table and join it with this table and create there the field that evaluates to true/false for furture months.

Is there an easy and fast way to do this? I suppose its a measure. The link you sent above in M code did not really help me, sorry.

[Dev13 · Accepted solution]

@edhans I followed the solution I posted above. Made a second calendar table and put a "relative date" filter in the visual filters pane. This worked for me.

Thank you for the advice.