Show all months, even if they are not in the filter

[SamVH12]

Hi all,

I have a model like this

I would like the chart to always display months from the minimum month selected in the filter to the last month of the maximum year selected in the filter, even if only a few months are selected and actual values haven't fully covered the whole year yet.

For example:

If I select the filter from 01-Dec-2024 to 31-Mar-2025, the chart I created displays only the data corresponding to the filter selection (Except for the Actual Sales YTD figure, it is not as expected. I'll explain at the end)

However, the chart should display:

• Actual sales: Values only within the selected filter period.
• Target sales: Data from the selected starting month through December 2025.
• YTD actual sales: Accumulated values from the selected starting month through December 2025. (My YTD measure is incorrect. I used the TotalYTD function for Actual Sales YTD, but it only calculates from the first day of the year instead of the minimum date selected in the filter. It should display as: Dec-24 = 23, Jan-25 = 23 + 162, Feb-25 = 23 + 162 + 89, and Mar-25 = 23 + 162 + 89 + 128)

Here is my sample data:

https://drive.google.com/file/d/1Z6R-9amF-zxq0eDguhoVN2rz0qZwh3yR/view?usp=sharing

Do we have any ways to achieve this, for both the display chart to also show the months from April-25 to Dec-25 that I would like, and the YTD calculation?

Many thanks in advance!

[Bibiano_Geraldo · Accepted solution]

Hi @SamVH12 ,

Let me know if attached file attend your need. if yes, give me a touch for explanations:

[v-echaithra]

Hi @SamVH12 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?, and please try to provide sample data.
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra.

[speedramps]

Hi again Sam

You need to learn to how share your example data via drop box or Onedrive,

and give clearer examples of the desired output.
Keep it simple and try only aks one question per ticket, rather than an entire project spec.
You will get quick and better answers without misunderstandings if you put more time, care and attention into questions.  I suggest you close this confussing sprawling ticket and start again. Thank you
If you quote @speedrampos in the new question then I will receive an automnated notification.

In the meantime I have created this PBIX Onedrive, 
Click here 

I have created a better Calendar table with a month offset

I have also transformed the Targer year to date

Then created start schema with 1:M relationshis form the dimenstion tables to the fact tables.

You can then use the offset in the Calendar in DAX to get the previous months sales

Sales PM = 
// this gets sales for the previous month

// get cursor month offset
var TM = MIN('Calendar'[Month offset])
// get previous offset
var PM = TM - 1
RETURN 
// use calculate with ALL to overide the natural filter context with LM offset
CALCULATE(
    SUM('Actual Sales'[value]),
     All('Calendar'),
    'Calendar'[Month offset] = PM
)

Or you can the the previous 3 months sales

Sales P3M = 
// this get sales for previous 3 months

// get cursor month offset
var TM = MIN('Calendar'[Month offset])

// create a temp table for last 3 month
var daterange = 
FILTER(ALL('Calendar'), 
'Calendar'[Month offset] >= TM  - 4 &&
'Calendar'[Month offset] <= TM  - 1
)

RETURN 
// use calculate to get sales in the previous 3 month daterange
CALCULATE(
    SUM('Actual Sales'[value]),
    daterange
)

Whilst I appreciate this is not exactly what you wanted, I do feel that these tricks will help you.

Watch these Youtube videos to learn more about offsets. 
They are a game changer, and important for any novice Power BI Developer.  
https://youtu.be/XjVLaVLluYE?si=FLThs12F4Y9j5r-j 

Please be nice and click the thumbs up because I tried to
and click [accept solution] if any of these hints and tips help,

You can accept multiple solutions from helpers, to show your appreciation.

[SamVH12]

@speedramps 

Thank you for your effort, but it does not fully meet my needs. However, it is a good trick to learn, and I will keep it in mind.

[Bibiano_Geraldo]

Hi @SamVH12 ,

Can you please share a sample file with no sensitive data?

[SamVH12]

Hi @Bibiano_Geraldo, I just edited the post and added the Drive link with sample data

[Bibiano_Geraldo · Accepted solution]

Hi @SamVH12 ,

Let me know if attached file attend your need. if yes, give me a touch for explanations:

[SamVH12 · Accepted solution]

Hi @Bibiano_Geraldo,

After checking the file you attached, I noticed a small issue. Even when I select from Jan 2020 - May 2024 in the slicer, the chart only displays the first month/year that has data in the actual sales or target sales table (Jan 2024), and the last month is Dec 2025. Instead of using the minimum month (Jan 2020) and the last month (Dec 2024) of the maximum year from the slicer, the chart does not adjust as expected.

However, your response gave me insight to solve my requirement. I modified the logic slightly, instead of using datetable in the chart and datetable2 as the slicer, I swapped them and adjusted both the isgreater and related measures accordingly. 

Now it works! Anyway, thank you for your effort, I really appreciate it!

[speedramps]

Try this ...

• Delete all your relationships
• Change your "year" and "DateID" field to date (this is easy to do in Power Query)
• Add a Year column to your Calendar table (this is easy to do in Power Query
• Create 1:M relationship from Region to Actual
• Create 1:M relationship from Region to Target
•  Create 1:M relationship from Calendar to Actual
• Create 1:M relationship from Calendar to Target

The create dax masure

Cummulative sales =
// get end of month date for the bar graph block
var myperiodend = MAX('Calendar'[date])
RETURN

// use ALL to override the natural month filter contexr
// then sum all values upto each period end
CALCULATE (
 SUM('Actual'[Value]),
 ALL('Calendar').
 'Calendar'[date] <= myperiodend 
)

If you want to use slicers then use ALLSELCTETD instaed of ALL

I want to help you more you have been a bit vague. 😀
Please provide example data and desrired output,

with a clear step-by-step desrciption how the 733 is calculated.
You will get quicker and better answers with no misundestandings if you provide clear examples.

Please be nice and click thumbs up foe me trying to help,

and then click [accept solution] if the solution works.

Many thanks. 😁

[SamVH12]

Hi @speedramps ,

I used the measure you suggested, and the YTD is fixed. Thank you for this!

733 is the sales target value, and it's fine with me, no need to adjust it

Now, I wonder how I can show the months from April 2025 to December 2025, while my slicer is only selecting from December 2024 to March 2025
Besides, I added a Drive link for the sample. If you don't mind, please access the link and download the pbix file, since I can't upload it directly here