Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I have a DAX calculation where I use a slectedvalue() from a month slicer.
If I just return the selectedvalue() itself, it returns the selected month from slicer, but when I try to use this selectedvalue in a DAX condition, it never works.
My Data:
1. Calendar Table :
2. Sales Table:
What I Want:
Sum the sales for each month , but only upto the selected month (in month slicer).
For example if I select February, I should see total sales of January and February only.
My current DAX Code :
Sales by Selected Month =
VAR SelectedMonth = SLECTEDVALUE('Calendar'[date].[Month])
RETURN
CALCULATE(
SUM('Table'[Sales(USD)]),
FILTER(
'Table',
'Table'[date].[Month] <= SelectedMonth
)
)
In the above Dax code, in the calculate function instead of using SlectedMonth variable, if I use a static month number like 2, it works but when I use the variable it returns the sum of sales for the whole year.
@all
Solved! Go to Solution.
Hi @Gondal-756
It wasn't easy to make the slicer based on the calendar table AND have the visual display rows that AREN'T from the selected month.
I based the rows on the values in the table that was originally set up as a disconnected slicer. (renamed to 'MonthList')
The measure is as follows:
xInnerCalc =
VAR _Dcurr = MAX( 'Date'[YearMonth] )
VAR _Curr = SELECTEDVALUE( 'MonthList'[YearMonth] )
VAR _Table =
FILTER(
SUMMARIZE(
ALL( 'Date' ),
'Date'[YearMonth],
"__Amt",
SUM( 'Sales'[Sales(USD)] )
),
[YearMonth] = _Curr
)
VAR _Total =
SUMX(
_Table,
[__Amt]
)
VAR _Result =
IF(
_Curr <= _Dcurr,
_Total
)
RETURN
_Result
To handle the totals, I created the following measure.
xSales =
SUMX(
'MonthList',
[xInnerCalc]
)
Let me know if this helps.
Hi @gmsambornt Thanks a lot, for your efforts.
I have following questions:
1. Is it must to have a separate slicer table? Can I just use the Date table for slicer directly?
2. In my dashboard, I would use the month slicer for other visuals as well. Since this is a different application of slicer, I can't keep the slicer interaction enabled with this visualization. I want to use the selected slicer month value indirectly using Dax.
In your solution, when I disable the interaction of slicer with visualization, nothing works and it shows all the data. Can we somehow make it work while keeping the interaction disabled?
Hi @Gondal-756
It wasn't easy to make the slicer based on the calendar table AND have the visual display rows that AREN'T from the selected month.
I based the rows on the values in the table that was originally set up as a disconnected slicer. (renamed to 'MonthList')
The measure is as follows:
xInnerCalc =
VAR _Dcurr = MAX( 'Date'[YearMonth] )
VAR _Curr = SELECTEDVALUE( 'MonthList'[YearMonth] )
VAR _Table =
FILTER(
SUMMARIZE(
ALL( 'Date' ),
'Date'[YearMonth],
"__Amt",
SUM( 'Sales'[Sales(USD)] )
),
[YearMonth] = _Curr
)
VAR _Total =
SUMX(
_Table,
[__Amt]
)
VAR _Result =
IF(
_Curr <= _Dcurr,
_Total
)
RETURN
_Result
To handle the totals, I created the following measure.
xSales =
SUMX(
'MonthList',
[xInnerCalc]
)
Let me know if this helps.
Hi @Gondal-756
In my solution, I used a date table as well as a disconnected slicer table.
1. Create a well-formed Date table. The date table you listed isn't a date table. (Even if you aren't using time intelligence calculations, it is a best practice to use a well-formed date table.)
In order to use any time intelligence calculation, you need a well-formed date table. The Date table must satisfy the following requirements:
2. Make sure your ‘Sales’[Date] has a Date data-type, not DateTime. If you need the time portion, duplicate the DateTime column and change to Date. Rename the columns accordingly.
3. Create a 1:* (one direction) relationship between ‘Date’[Date] and ‘Sales’[Date].
4. Create a table that is a list of [MMM-YY] and [YearMonth] from ‘Date’ to be used in a slicer. ( [Month] and [MonthNo] could be used if you prefer. My preference would be to handle multiple years.)
SlicerMonths =
SUMMARIZE(
'Date',
'Date'[MMM-YY],
'Date'[YearMonth]
)
5. Create the following measure:
Sales by Selected Month =
VAR _SelectedMonth = MAX( 'SlicerMonths'[YearMonth] )
RETURN
CALCULATE(
SUM( 'Sales'[Sales(USD)] ),
FILTER(
ALL( 'Date'[YearMonth] ),
'Date'[YearMonth] <= _SelectedMonth
)
)
Let me know if you have any questions.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
9 |