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 there,
The scenario I have is I have a list of transactions for multiple customers, date ranges . I require a filter on this dataset to meet two conditions.
1. The customer must have transacted in the selected month
2. Return transactions for the previous six months.
So for example the month is July, I have transacted in this month, so the report will return transactions from Feb to July inclusive. However I then select June, as I have not transacted in this month no rows returned.
I have tried different methods using datesbetween with if conditions, calculated tables but cannot seem to create a measure or table that will meet both conditions.
Anyone any suggestions.
No sample data available.
thanks
It's a bit hard to envision without knowing your table structure and how you're planning to use this, but (without having tested) you could probably use something along these lines:
Measure Name =
//Store customer ID inherited from current row/visual/slicer context
Var CID = max('table'[Customer ID])
//Store Month inherited from current row/visual/slicer context (assumes you have a column for month already)
Var M = max('table'[Month])
//Calculate the last day of the stored month (EOMONTH wrapper is included just in case your table does not include transactions up to the last day of the month. Would not be needed if you are using a date dimension)
Var D = EOMONTH(Calculate(Max('Table'[Transaction Date]),all('Table'),'Table'[Month]=M),0)
//Use EOMONTH to calculate a -6 month offset date
Var DOffset = EOMONTH(D,-6)
//Count transaction ID for customer where in the selected month
Var TCount = Calculate(Count('table'[Transaction ID]),all('table'),'table'[Month] = M,'Table'[Customer ID]= CID)
//Return variables
RETURN
//Check if TCount is greater than 0
if(TCount > 0,
//If TCount is greater than 0 then return "Include" for any records where the [Transaction Date] is between D and DOffset dates else return "Exclude"
if('Table'[Transaction Date] <= D && 'Table'[Transaction Date]>= Date(Year(DOffset),Month(DOffset),1),"Include","Exclude"),
//If TCount <=0 then returns "Exclude". Filter measure to "Include" only.
"Exclude")
thanks @halfglassdarkly
I did have an earlier version using count of transactions for current month then do xyz. Ill see if can adapt. Appreciate lack of data visibility, but effectively a flat transaction table with a date dimension.
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |