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,
I have a Date table that contains formatted dates, but also contains text strings as in "2017-11" and "2017-Q4", etc. This is because we use a 4-4-5 fiscal calendar. So if the date is 11/27/17, the fiscal month is 12 and the Year-Mo is 2017-12.
My fact (Invoices) table also has these text fields which are associated with the Invoice Date. What I'm trying to do is this, but it's not working:
MTD Sales = calculate(sum(Invoices[Ext Sales Amount]),filter(Invoices,Invoices[Year-Mo]=DATES[Year-Mo]))
The reason it's not working is because I'm trying to compare two strings as opposed to 2 values. The measure will accept a string for the left side of the equal sign, Invoices[Year-Mo], but not for the right side of the equal sign DATES[Year-Mo]. How can I get this to work? I'm working with a fact table that has multiple years so that's why I need to use the "YYYY-MM" string to compare.
Rose
Solved! Go to Solution.
DAX0110,
Your measure worked with a slight alteration. The only thing I had to change was that my variables had to look at the Invoice Date and Invoice Year-Mo in order to compare it to my DATES table (which is actually called PBI_FSCAPF....don't ask me I didn't name it!):
Here's my finished measure:
TY Month Sales =
VAR CurrDate = MAX( PBI_Invoice_Multi[Formatted Inv Date] )
VAR CurrMonth = MAX( PBI_Invoice_Multi[Year-Mo] )
RETURN
calculate(sum([Ext Sales Amount]),All(PBI_FSCAPF),PBI_FSCAPF[YEAR-MO] = CurrMonth,PBI_FSCAPF[LUDATE]<=CurrDate-1)
Thanks for your help!
Rose
Hi @Roseventura, you could try this version:
MTD Sales =
VAR currentDate = MAX( DATES[Date] )
VAR currentMonth = MAX( DATES[Year-Mo] )
RETURN
calculate(
sum( Invoices[Ext Sales Amount] )
, ALL(DATES)
, DATES[Year-Mo] = currentMonth
, DATES[Date] <= currentDate
)
DAX0110,
Your measure worked with a slight alteration. The only thing I had to change was that my variables had to look at the Invoice Date and Invoice Year-Mo in order to compare it to my DATES table (which is actually called PBI_FSCAPF....don't ask me I didn't name it!):
Here's my finished measure:
TY Month Sales =
VAR CurrDate = MAX( PBI_Invoice_Multi[Formatted Inv Date] )
VAR CurrMonth = MAX( PBI_Invoice_Multi[Year-Mo] )
RETURN
calculate(sum([Ext Sales Amount]),All(PBI_FSCAPF),PBI_FSCAPF[YEAR-MO] = CurrMonth,PBI_FSCAPF[LUDATE]<=CurrDate-1)
Thanks for your help!
Rose
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 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |