Between 2 dates in FILTER function

[gazz9ar]

Hi there, 

I need to filter a table based on a selected MONTH and YEAR,  the user can select multiple months also.

 This is my measure:

Total Checks (SINDESC) Filtered =

VAR selectedyear = SELECTEDVALUE(Dates[Año])

VAR selectedmonth = ALLSELECTED(Dates[MonthNum])

return

CALCULATE([Total Checks (SINDESC)];

FILTER(Cobros; Cobros[Fecha Contabilizacion] IN DATESBETWEEN(Cobros[Fecha Contabilizacion];

DATE(selectedyear;selectedmonth;"01");DATE(selectedyear;selectedmonth;"31")));

USERELATIONSHIP(Dates[Date];Cobros[Fecha Vencimiento Cheque]))

Of course this measure is not working. First problem is, how do i get the last day of the month or months selected? And if the user selects multiple months it will not work.

I dont know if i should be using FILTER function to filter a date, but i need to calculate [Total Checks (SINDESC)] within a range of dates.

Thanks in advance guys

[gazz9ar · Accepted solution]

Guys the problem was solved in another post  i did.

Link to solution: https://community.powerbi.com/t5/Desktop/Measure-with-multiple-dates/m-p/892328#M427770

Thank you so much for the help.

[amitchandak]

If your date table is joined with your fact/data table. Why do need to filter. If you use month and year slicer from Date table it should work.

What is the logic you need?  EOMONTH, and ENDOFMONTH can be used for the end date of the month. please find the example below to see how it is used

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-12,MONTH))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date Filer])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date Filer],-12,MONTH),"8/31")))

[gazz9ar]

I have to Filter because as you can see, im using USERELATIONSHIP after.

First, i obtain every record based on month and year selected by the user.

Later  i want visualize the SUM of those records based on other date column in the fact table.

[v-frfei-msft]

Hi @gazz9ar ,

Try this one please.

Total Checks (SINDESC) Filtered =
VAR selectedyear =
    SELECTEDVALUE ( Dates[Año] )
VAR selectedmonth =
    ALLSELECTED ( Dates[MonthNum] )
RETURN
    CALCULATE (
        [Total Checks (SINDESC)];
        FILTER (
            Cobros;
            Cobros[Fecha Contabilizacion] >= DATE ( selectedyear; selectedmonth; "01" )
                && Cobros[Fecha Contabilizacion] <= DATE ( selectedyear; selectedmonth; "31" )
        );
        USERELATIONSHIP ( Dates[Date]; Cobros[Fecha Vencimiento Cheque] )
    )

If it doesn't meet your requirement,  Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[gazz9ar]

@v-frfei-msft  sorry for spamming you, i just want to be clear

Here is a image of what i want to do:

The table will change as you change the period, and the bar chart should change based on the "Fecha Vencimiento" column too.

I dont need the table, is just a visual help

[gazz9ar]

Thanks @v-frfei-msft , sadly it threw an error.  Anyway i tried a measure with the same structure and the problem still in the slicer and the visual.

Please check the file, the page is called HERE. (it is in Spanish, but i think you will understand, i left you notes).

Pbix File: https://1drv.ms/u/s!AttE7DRi4S6HilwL7h2gEeXlZBF-?e=XIiC7N

Thank you.

[gazz9ar]

Here is my measure, that is working.

 It is the SUM of every Check, filtered by month and year. 

Here you can see in a table the  Total for every date in "Fecha Vencimiento Cheque", how should i group by that date in the given visual?

NOTE: if i add to the CALCULATE a filter with USERELATIONSHIP, it shows a wrong Total ($1million ), you can check the records.

BUT the visual should group by that Date.  Im starting to think it is not possible 😞