Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello guys,
I have been trying to create a calculated measure which should be filtered based on another measure. Here's my scenario
1. I want to create Total YTD of a column which I am doing by below snippet
column = Apr18[Revenue] - Apr18[Actuals] YTD Revenue = TOTALYTD(SUM(Apr18[column]),Apr18[Datekey])
The Above code gives me the YTD Revenue when none of the slicers are selected. But in my case, I have two slicers which are Year and Month based on which the YTD Revenue measure has to get modified.
2. For this purpose I created the following two measures using SELECTED VALUE DAX as shown below
SelectedYear = SELECTEDVALUE(Apr18[Year]) SelectedMonth = SELECTEDVALUE(Apr18[Month])
Using the above two measures I tried to filter the YTD Revenue measure. I tried different approaches (even those from various others posts in the forum) whose codes as follows. But nothing seem to work when I make a selection in the slicers for Year and Month
YTD Revenue = TOTALYTD(SUM(Apr18[column]),Apr18[Datekey],FILTER(Apr18,Apr18[Year]=SELECTEDVALUE(Apr18[Year]) && Apr18[Month]<=[SelectedMonth]))
Then I brought the Slected Month as a Column and tried using that as well as given below
YTD Revenue = TOTALYTD(SUM(Apr18[column]),Apr18[Datekey],FILTER(Apr18,Apr18[Year]=SELECTEDVALUE(Apr18[Year]) && Apr18[Month]<=Apr18[filtermonth]))
The following codes also I tried but with no needed result
YTD Revenue = var currYear= MAX(Apr18[Year]) var currMonth=Max(Apr18[Month]) return if(AND(currYear=[SelectedYear],currMonth<=[SelectedMonth]), CALCULATE(TOTALYTD(SUM(Apr18[column]),Apr18[Datekey]),FILTER(Apr18,Apr18[Year]=SELECTEDVALUE(Apr18[Year]) && Apr18[Month]<=MAX(Apr18[filtermonth]))))
YTD Revenue = var currYear= MAX(Apr18[Year]) var currMonth=Max(Apr18[Month]) return if(AND(currYear=[SelectedYear],currMonth=[SelectedMonth]), CALCULATE(TOTALYTD(SUM(Apr18[column]),Apr18[Datekey])))
YTD Revenue = var y = [SelectedYear] var m = [SelectedMonth] RETURN TOTALYTD(SUM(Apr18[column]),Apr18[Datekey],FILTER(Apr18,Apr18[Year]=y && Apr18[Month]<=m))
All these measures are getting filtered by month whenever I make a selection in the month column
Here's the look at my data
Year | Reportung Month | Revenue | Actuals | Datekey | Month | Month Name |
2018 | Feb | 749378 | 129760 | 2/1/2018 | 2 | February |
2018 | Feb | 135205 | 332767 | 2/1/2018 | 2 | February |
2018 | Feb | 168502 | 57470 | 2/1/2018 | 2 | February |
2018 | Feb | 547442 | 332750 | 2/1/2018 | 2 | February |
2018 | Mar | 748155 | 128537 | 3/1/2018 | 3 | March |
2018 | Mar | 133982 | 331544 | 3/1/2018 | 3 | March |
2018 | Mar | 167279 | 56247 | 3/1/2018 | 3 | March |
2018 | Mar | 546219 | 331527 | 3/1/2018 | 3 | March |
2018 | Apr | 759243 | 139625 | 4/1/2018 | 4 | April |
2018 | Apr | 145070 | 342632 | 4/1/2018 | 4 | April |
2018 | Apr | 178367 | 67335 | 4/1/2018 | 4 | April |
2018 | Apr | 557307 | 342615 | 4/1/2018 | 4 | April |
2017 | Dec | 659832 | 123525 | 12/1/2017 | 12 | December |
2017 | Dec | 45659 | 326532 | 12/1/2017 | 12 | December |
2017 | Dec | 78956 | 51235 | 12/1/2017 | 12 | December |
2017 | Dec | 457896 | 326515 | 12/1/2017 | 12 | December |
2017 | Nov | 658609 | 122302 | 11/1/2017 | 11 | November |
2017 | Nov | 44436 | 325309 | 11/1/2017 | 11 | November |
2017 | Nov | 77733 | 50012 | 11/1/2017 | 11 | November |
2017 | Nov | 456673 | 325292 | 11/1/2017 | 11 | November |
2017 | Oct | 669697 | 133390 | 10/1/2017 | 10 | October |
2017 | Oct | 55524 | 336397 | 10/1/2017 | 10 | October |
2017 | Oct | 88821 | 61100 | 10/1/2017 | 10 | October |
2017 | Oct | 467761 | 336380 | 10/1/2017 | 10 | October |
The Below are the screenshots of my PBI Desktop.
Only when year is selected, the report data looks fine. In this case, the YTD Revenue in the card is the sum of column value for Feb, Mar and Apr.
But when a month is selected, the card gets filtered for that month. Ideally the way that I am expecting it to work is, when Mar is selected, the measure in the card should show me the sum of values of column for the months of Feb and Mar and when Feb is selected, it should show the value of only Feb. The Case is similar for 2017 as well
i.e. as highlighted, when Mar is selected, the card should display 1495560 and not 747780.
Any help here is appreciated. Thanks in advance.
Hope the information I have provided is sufficient enough for helping me
Note: The Card works this way when we apply advanced filter of less than or equal in Page level filters. But unfortunately I can't use that in my case
Solved! Go to Solution.
Hello,
What probably would work perfectly is a separate DateTable with contiguous Dates. Then you could add the DateTable columns to your PivotTable and use it for TOTALYTD.
After trying a little bit this formula gave me the correct result as well:
YTD Revenue:=VAR LatestDate=MAX(FactTable[Datekey]) RETURN CALCULATE(SUM([column]);
CALCULATETABLE(
FILTER(FactTable;FactTable[Datekey]<=LatestDate&&YEAR([Datekey])=YEAR(LatestDate));
ALL(FactTable)
)
)
Hello,
maybe I haven't understood your problem totally.
My first question is why don't you add a timeline instead of separate slicers for month and year?
Revenue YTD=TOTALYTD([Measure],Table[DateKey])
If you want to use slicer instead of timeline.
You can add:
Revenue YTD=IF(HASONEVALUE(Table[Year],
TOTALYTD([Measure],Table[DateKey]),
BLANK())
So it should automatically give you your YTD value if only one year is selected.
Hi,
I am fine with using the below code snippet
Revenue YTD=TOTALYTD([Measure],Table[DateKey])
And this is also giving YTD values for the year selections.
i.e. Say I am having 3 years.
2016
2017
2018
When I select 2018, this gives me YTD values for 2018, When 2017 is selected, YTD values for 2017 is shown and so on.
But my requirement is in addition to the year selection, it should also respond to the month selections made
The Following is the step-by-step process of potential selection of slicers
i.e.
1. Firstly, If 2018 and Apr are selected in slicers, my YTD Value should be the Value from Jan-2018 to Apr-2018
2. Now if my Month slicer is changed to Mar, my YTD Value should be from Jan-2018 to Mar-2018
3. Lastly, If I change my year to 2017, the card should display YTD Value from Jan-2017 to Mar-2017, since I have already selected my Month to March in the previous step.
Hope I am clear this time!!!
Hello,
you are clear.
What happens actually if you select a month via slicer?
Hi,
The Measure gets filtered for the Month, instead of bringing all the data of months less than that month
Please refer the screenshot posted above in the post for the same
Hello,
What probably would work perfectly is a separate DateTable with contiguous Dates. Then you could add the DateTable columns to your PivotTable and use it for TOTALYTD.
After trying a little bit this formula gave me the correct result as well:
YTD Revenue:=VAR LatestDate=MAX(FactTable[Datekey]) RETURN CALCULATE(SUM([column]);
CALCULATETABLE(
FILTER(FactTable;FactTable[Datekey]<=LatestDate&&YEAR([Datekey])=YEAR(LatestDate));
ALL(FactTable)
)
)
Hi, I have a similar question with text filter and I know it's pretty stupid but I am not able to figure out a simple thing to do in DAX.
In the image above, I want to calculate Weighted Sales filtered on Types
Current formula for Weighted Sales = Sales/ Sum(Sales)
I am trying to do Weighted Sales = Sales/ [Sum(Sales), filtered by (Type)] - I want to see the weighted sales by types.
Whenever I am using DAX, I need to use Divide function and for numerators and denominators in it, I have either use SUM() or similar funcitons to perform calculation, I cannot do simple Column1/Column2.
Can anyone please help me in this?
This thing works fine. Can you pls. explain this DAX Usage?
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |