how to use earliest in a calculate filter?

[Anonymous]

my end goal is to get a running total over months.
i have 1st table = months
2nd table = dataMonth, Measure, Colors, PatientID

my chart can correctly show a count of patients for a measure for color = green FOR THAT MONTH

(edited - the desired Green count for row 2 should = 2)

^^ this shows an example when i have a relationship between table1>Months and table2data>dataMonth

if i remove that relationship i get a total of green over all time every month.
when ever i use keyword EARLIEST(... it does not recognize anything i put in the parameters.
should it be seeing all columns in my output table above? or does it have to be a certain type of colum/measure?

also, any advice on how to do a running total by any methods.
i am also going to look at SUMMARY to see if that can get the running totals into each row.

i am looking at this example but cannot get the EARLIEST to work
https://javierguillen.wordpress.com/2012/02/06/can-earlier-be-used-in-dax-measures/

[Anonymous · Accepted solution]

i have been able to figure this out just by using VARs instead

Numerator = 
VAR CurrentMonth = max(PATIENT_MEASURE_SAMPLE[created_month])
VAR LastYearMonth = SAMEPERIODLASTYEAR(PATIENT_MEASURE_SAMPLE[created_month])
VAR v =
CALCULATE(DISTINCTCOUNT([PATIENT_KEY]),PATIENT_MEASURE_SAMPLE[STATUS_CODE] = "Green",
PATIENT_MEASURE_SAMPLE[created_month] >= LastYearMonth,

PATIENT_MEASURE_SAMPLE[created_month] <= CurrentMonth

)+ 0
return v

[Anonymous]

I dont think I have ever seen anything other than EARILIER used as that would required a deep deep understanding on how context changes in your measure. If you are just looking for a running total ( be it month, quarter, year) why not just use the built-in time intelligence functions. 

https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax

Just need to get a full date table:

https://docs.microsoft.com/en-us/dax/calendar-function-dax

and relate that your main table. Then use the columns from that date table as filters. 

[Anonymous]

sorry, you seemed to just repeat what i am asking.
user time functions - yes i want to but need correct syntax for it to work.
use calendar for months - yes that is my table1 > months

how to use earliest / earlier to filter my results correctly?
both functions give me errors such as "cannot find [Months]" actually any column or measure i put in there it says that.

my AXIS is the table1 > Months
my value (data) is table2 > created_month
i think i need to filter when created_month >= SamePeriodLastYear(Months) or EARLIER(Months)

i cannot get any of these to work without syntax errors

[Anonymous]

Your current calendar table will not suffice. You need to have a calendar table at the day level that is contigous, which ideal would go from the  beginning of the year to the of the year.  The following DAX code can be modified to create such a calendar. Just need to change out the Orders[Order Date] part

DimCalendar =
ADDCOLUMNS(
    CALENDAR(
        DATE( YEAR(MIN( Orders[Order Date])),1,1),
        DATE( YEAR( MAX ( Orders[Order Date])),12,31)),
        "Year", YEAR([Date]),
        "Month", MONTH( [Date] ),
        "MonthName", FORMAT( [Date], "mmmm"),
        "Quarter", "Q" &ROUNDUP( MONTH([Date])/3,0)
)

After you create that table, you then mark it as such:

This table then needs to be related to your fact table in some way. 

Once that is all done then you can write a measure for MTD, QTD, YTD, etc.  For example:

MTD Sales (Using TOTALMTD) =
TOTALMTD([Total Sales],'Date'[Date])

QTD Sales (Using TotalQTD) =
TOTALQTD([Total Sales],'Date'[Date])

YTD Sales (Using TotalYTD) =
TOTALYTD([Total Sales],'Date'[Date])

Then based on what the filter context is (i.e. what date you have as filter) it will give you the answer you are looking for. Everything happens in context. There's really no need, and actually not a good idea, to store this type of information in calculated columns, but if you insist, you can modify this code:

YTD = 
VAR __CurrentDate= Table1[Date]
VAR __CurrentDummy = Table1[Dummy]
VAR __CurrentYear = YEAR(Table1[Date])

Return
CALCULATE(
    SUM( Table1[vol]),
    FILTER(
        ALL( Table1),
        __CurrentDate >= Table1[Date]
        && __CurrentDummy =  Table1[Dummy]
        && __CurrentYear = YEAR(Table1[Date])
    )
)

[Anonymous]

i may be close.
i did all the things you said (created table, set as date, made relationship), but couldn't get it to work on that DimCalendar[date]
am i suppose to put DimCalendar[date] into the axis or my data table [created_month]?
and which date to use in the TOTALYTD(..., which date here?)
when i put DimCalendar[date] into those i get no results
when i put [created_month] into both axis and measure i get results

the numbers do total going through the year correctly

BUT i need to get

numerator = totals from row date back 1 year
denominator = totals from row date back to beginning

1 other requirement but not manditory is to get each month to show. in the ^^ example output above it is missing months between.

[Anonymous]

can you upload an example of what you are working with?  There's too many things to go through and would be easier to see. Onedrive works well. 

[Anonymous]

i tried to over simplify this request.
what i didnt talk about is that my running total HAS to be a distinct count of PatientID over the last year, not just a simple running total.

Numerator = TOTALYTD(DISTINCTCOUNT(PATIENT_MEASURE_SAMPLE[PATIENT_KEY]),PATIENT_MEASURE_SAMPLE[created_month])

so unless the time functions re-evaluate the distinct counts from previous months this will not work for my requirements.

this file i share i can unlink the relationship to the date and each column gets access to all the PatientIDs over time, i just need to get the correct syntax to filter it to that row month - 1 year

if i leave the relationship i think i might need "summary" to get the previous PatientIDs and again would need to filter the results by month - 1 year

working file removed