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.
Hello Community,
I created a calculated column to return 1 or 0 based on whether the date is within last 28 days. However, as you see from my screenshot below, it's showing all as 1. Below are all the DAX I'm using to populate this chart. Any pointer where I might have incorrectly implemented the formula?
[DateAxis] - date column format as DateTime
zLast28Days =
IF(
Data_RootTable_ChangeToYourSource[DateAxis] >= Data_RootTable_ChangeToYourSource[zMinDate] &&
Data_RootTable_ChangeToYourSource[DateAxis] <= Data_RootTable_ChangeToYourSource[zMaxDate]
, 1 , 0 )
zMaxDate = LASTDATE(Data_RootTable_ChangeToYourSource[DateAxis])
zMinDate = DATEADD(LASTDATE(Data_RootTable_ChangeToYourSource[DateAxis]),-28,DAY)
On a separate note - If I were to populate this "filtered last 28 day list" as a table, what's the best way to go about it?
Your help is appreciated.
Solved! Go to Solution.
I knew it shouldn't be that hard - firstnonblank, calculatetable - all those are not needed.
Fixed my own trouble simply by switching column to measure.
maxdate=lastdate(all('calendar'[lawcalendarfull]))
mindate=dateadd(lastdate(all('calendar'[lawcalendarfull])),-6,day)
last7=if(calendar[lawcalendarfull]>=calendar[lawmindate]&&calendar[lawcalendarfull]<=calendar[lawmaxdate],1,0
I'm curious if there's a better way to compose these DAX to filter my last N days from Calendar dim? Appreciated!
Hi @captainlaw,
In addition to Vvelarde's solution, you can first add an Index Column for your Calendar dim table on the Query Editor.
Then, you should be able to use the formula below to create the calculate column.
IsLast28days = VAR maxDay = CALCULATE ( MAX ( 'Calendar Table'[Index] ), ALL ( 'Calendar Table' ) ) RETURN IF ( 'Calendar Table'[Index] > maxDay - 28 && 'Calendar Table'[Index] <= maxDay, 1, 0 )
And use the formula below to create a new table with the last 28 days mentioned above.
Last28daysTable = CALCULATETABLE ( 'Calendar Table', 'Calendar Table'[IsLast28days] = 1 )
Regards
I'm trying to attach the desktop pbix file for anyone who would like to peek under the hood, however, I'm NOT seeing the attachment option. I see photos and video... where is attachment option within this forum?
Hi,
Last28days = VAR zMaxDate = LASTDATE(all(TableDays[DateAxis])) VAR zMinDate = DATEADD(zMaxDate,-28;DAY) RETURN IF( TableDays[DateAxis] > zMinDate&& TableDays[DateAxis] <= zMaxDate , 1 ; 0 )
And to create a new table with the last 28 days
Last28daysTable = CALCULATETABLE(TableDays,TableDays[Last28days]=1)
Hi,
I have followed same method but am using Month instead of Day its working fine when i have mentioned the month value as -1 it exactly shows 2 month records but if the value is -2 its pulling all historic data.
Below steps i have followed
Column created for last 3 months:
Last3Month =
VAR zMaxDate = LASTDATE(all(Sheet1[Date]))
VAR zMinDate = DATEADD(zMaxDate,-2,MONTH)
RETURN
IF(
Sheet1[Date]> zMinDate&&
Sheet1[Date] <= zMaxDate
, 1 , 0 )
New table - for 3 months data only
Last28daysTable = CALCULATETABLE(Sheet1,Sheet1[Last3Month]=1)
I have to pull the last 3 month records alone.
Regards,
Yuvaraj
Thank you all for your help.
I'm getting "blank" value when I use the following DAX formula for my date. I'm trying to return a date a week ago from the latest date using the following formula.
ErrorBlank = dateadd('calendar'[LAWMaxDate],-6,DAY)
The following is returning values :
LAWMaxDate = LASTDATE(ALL('calendar'[LAWCalendarFull]))
Any help is appreciated!
Any idea on this blank DAX that I'm experiencing? I can attach the pbix file if it helps.
Hi @captainlaw,
Could you try the formula below to see if it works.
Measure = VAR LAWMaxDate = LASTDATE ( ALL ( 'calendar'[LAWCalendarFull] ) ) RETURN FIRSTNONBLANK ( CALCULATETABLE ( DATEADD ( 'calendar'[LAWCalendarFull], -6, DAY ), FILTER ( calendar, 'calendar'[LAWCalendarFull] = LAWMaxDate ) ), 1 )
Regards
It works! But I'm not aware of the logic of the "blank" in the previous metric. Why would it show blank when LAWMaxDate has value? Does it mean CALCULATETABLE is required? How do you determine if it is required or not?
I knew it shouldn't be that hard - firstnonblank, calculatetable - all those are not needed.
Fixed my own trouble simply by switching column to measure.
maxdate=lastdate(all('calendar'[lawcalendarfull]))
mindate=dateadd(lastdate(all('calendar'[lawcalendarfull])),-6,day)
last7=if(calendar[lawcalendarfull]>=calendar[lawmindate]&&calendar[lawcalendarfull]<=calendar[lawmaxdate],1,0
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |