Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I just came up with the issue I am not skilled enough to solve 😉 It mainly involves making two nested calculations which I cannot deal with.
Let's say I have dataset with structure similar to following (ItemID, Date (sorry for my locale), Category)
What I need is to create a measure that would:
- count distinct ItemIDS in given month
- return maximum value of monthly count over last 2 months.
- it should be measure, as I want to include slicer with category (and many more categoric columns) so it could be dynamic.
I want to achieve following results in visuals (here an example without including any slicer or filter):
01.01.2017 | 01.02.2017 | 01.03.2017 | 1.04.2017 | 1.05.2017 |
6 | 6 | 5 | 5 | 4 |
I have tried following:
Measure1 = MAXX(FILTER(Table; DATESINPERIOD ( Table[Date]; MAX( Table[Date] );-2; MONTH)); DISTINCTCOUNT(Table[ItemID]))
But it does not work.
Do you happen to know any trick?
Cheers,
M
Solved! Go to Solution.
Hi @minerito,
Please try to create the below measure:
Measure = VAR a = MAX ( 'Table'[Date] ) VAR b = SUMMARIZE ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] >= DATE ( YEAR ( a ), MONTH ( a ) - 1, 1 ) && 'Table'[Date] <= DATE ( YEAR ( a ), MONTH ( a ) + 1, 1 ) ), 'Table'[Date], "CountOfItems", DISTINCTCOUNT ( 'Table'[ItemID] ) ) VAR c = MAXX ( b, [CountOfItems] ) RETURN c |
Best Regards
Rena
Try like
Measure1 = MAXX(FILTER(values(Table[Date]); DATESINPERIOD ( Table[Date]; MAX( Table[Date] );-2; MONTH)); DISTINCTCOUNT(Table[ItemID]))
//OR
Measure1 = calculate(MAXX(FILTER(Table; DATESINPERIOD ( Table[Date]; MAX( Table[Date] );-2; MONTH)); DISTINCTCOUNT(Table[ItemID])),values(Table[Date]))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
Hi,
Thanks for help - unfortunately neither of them works. The first one dragged into vidual yields in 'can't display the visual', the second one is the same...
Hi @minerito,
Please try to create the below measure:
Measure = VAR a = MAX ( 'Table'[Date] ) VAR b = SUMMARIZE ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] >= DATE ( YEAR ( a ), MONTH ( a ) - 1, 1 ) && 'Table'[Date] <= DATE ( YEAR ( a ), MONTH ( a ) + 1, 1 ) ), 'Table'[Date], "CountOfItems", DISTINCTCOUNT ( 'Table'[ItemID] ) ) VAR c = MAXX ( b, [CountOfItems] ) RETURN c |
Best Regards
Rena
Hi Menerito, I am not sure if this link would be helpful for you https://community.powerbi.com/t5/Desktop/FILTER-vs-CALCULATETABLE/td-p/197093
I have a similar problem to you, but I need a column to return the value in last month of every single product. (FOR EXAMPLE, I need to return value from a [sale] column)I tried this and it doesn't work
lastmonth_sale = CALCULATE('table'[sale],FILTER(ALL('table'[sale],'table'[date] = PREVIOUSDAY(table'[date])))
or
lastmonth_sale = CALCULATE('table'[sale],FILTER(ALL('table'[sale],'table'[date] = EARLIER(table'[date],1)))
If anyone can help here?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |