cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

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

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?   

1 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.

View solution in original post

3 REPLIES 3
Super User III
Super User III

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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

@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.

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors