- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

DAX variable how to
I have the following datasets:
1) Calendar
2) Actual: captures actual monthly sales
Month | Value |
Jan | 1000 |
Feb | 1200 |
Mar | 800 |
etc |
3) Forecast: captures forecast sales for remaining months. Forecasts updated monthly. eg. In Jan, 12 months' of forecast. In Feb, 11 months' of forecast....etc
ActMth | ForecastMth | Value |
Feb | Mar | 500 |
Feb | Apr | 600 |
Feb | May | 550 |
Feb | Jun | 700 |
Feb | Jul | 750 |
Feb | Aug | 500 |
Feb | Sep | 500 |
Feb | Oct | 500 |
Feb | Nov | 560 |
Feb | Dec | 580 |
Mar | Apr | 700 |
Mar | May | 800 |
Mar | Jun | 1000 |
Mar | Jul | 1100 |
Mar | Aug | 1200 |
Mar | Sep | 800 |
Mar | Oct | 900 |
Mar | Nov | 950 |
Mar | Dec | 1300 |
Relationship with Calendar set for #2-3.
I have a slicer in my excel worksheet to select "month" from Calendar.
I would like to achieve the following:
1) When I select "month" on the slicer, result should show me actual sales and forecast for remaining months accordingly.
eg. if selected month = Mar, report to show actual sales for Jan-Mar, forecast for Apr-Dec
if selected month = Feb, report to show actual sales for Jan-Feb, forecast for Mar-Dec
Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Total Year | |
Sales |
Calculated measures created:
MTD Actual:=TOTALMTD(sum(Actual[Value]),'Calendar'[Date])
MTD Forecast:=calculate(TOTALMTD(sum(Forecast[Value]),'Calendar'[Date]),Forecast[ActMth]=3)
YTD Forecast:=calculate([YTD Actual],'Actual'[Date (Month Index)]<=3)+[MTD Forecast]
I have to manually change the month in the formula of "MTD forecast" and "YTD forecast". Pls advise how to set these 2 measures variable according to slicer selected month.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @Anonymous ,
According to your description, here's my solution.
1. Create a Month table. Sort Month column by MonthNo column.
2.Create a measure.
Sales =
IF (
MAX ( 'Month'[MonthNo] ) <= SELECTEDVALUE ( 'Date'[Month] ),
MAXX (
FILTER ( Actual, MONTH ( 'Actual'[Date] ) = MAX ( 'Month'[MonthNo] ) ),
'Actual'[YTD]
),
IF (
MAX ( 'Month'[Month] ) <> "Total Year",
MAXX (
FILTER (
'Forecast',
'Forecast'[ActMth] = SELECTEDVALUE ( 'Date'[Month] )
&& MONTH ( 'Forecast'[Date] ) = MAX ( 'Month'[MonthNo] )
),
'Forecast'[Value]
),
SUMX (
FILTER ( Actual, MONTH ( 'Actual'[Date] ) <= SELECTEDVALUE ( 'Date'[Month] ) ),
'Actual'[YTD]
)
+ SUMX (
FILTER (
'Forecast',
'Forecast'[ActMth] = SELECTEDVALUE ( 'Date'[Month] )
&& MONTH ( 'Forecast'[Date] ) > SELECTEDVALUE ( 'Date'[Month] )
),
'Forecast'[Value]
)
)
)
Put Month column from Month table in Matrix column and the measure in values, get the result:
I attach my sample below for your reference.
Best regards,
Community Support Team_yanjiang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @Anonymous ,
I'm not quite clear about the problem, as your description "Relationship with Calendar set for #2-3", do you mean the Calendar table have relationship with Actual table and also Calendar table have relationship with Forecast table? If so, which column are they connected, there's not date column in Actual and Forecast tables. Besides, I create a sample and attach it below, could you please reproduce your problem in my sample and send back.
Best regards,
Community Support Team_yanjiang
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Pls advise solution to technical issue included in the excel file, link below:

Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.

User | Count |
---|---|
10 | |
8 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
9 | |
9 |