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.
Good morning Everyone,
I'm currently trying to create a KPI card that can be affected by multiple slicers:
Emailable: Not Emailable, Emailable
Current Salesplan (groups): a grouped field based on a field with multiple text values
I want to show last months average order frequency with this formula: (365 Avg # of Transactions is a measure field averaging a field full of integers)
Solved! Go to Solution.
Hi @tyler_rogers_ds,
Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
In addition, did you work with calculated columns? If that's the case, current power bi does not support creating dynamic calculated column/table base do filters. They work on different levels and you can't use child level to affect their parent.
Notice: the data level of power bi.
Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)
Regards,
Xiaoxin Sheng
Hi @tyler_rogers_ds,
Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
In addition, did you work with calculated columns? If that's the case, current power bi does not support creating dynamic calculated column/table base do filters. They work on different levels and you can't use child level to affect their parent.
Notice: the data level of power bi.
Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)
Regards,
Xiaoxin Sheng
@tyler_rogers_ds , Not ver clear . you can use measure like this based on today . Assuming use of date table
This month Today =
var _min = eomonth(today(),-1)+1
var _max = eomonth(today(),0) //today()
return
CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )
Last month Today =
var _min = eomonth(today(),-2)+1
var _max = eomonth(today(),-1) //today()
return
CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )
Use all if there is filter
This month Today =
var _min = eomonth(today(),-1)+1
var _max = eomonth(today(),0) //today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )
Last month Today =
var _min = eomonth(today(),-2)+1
var _max = eomonth(today(),-1) //today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )
Hi amitchan,
My KPI card is still not being affected by this formula you've given me. I am currently not making use of a date table.
Furthermore, I am searching for last month only and previous month from last month only.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |