Forum Discussion

gazz9ar's avatar
gazz9ar
Frequent Visitor
6 years ago
Solved

Between 2 dates in FILTER function

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 ...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

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's avatar
    gazz9ar
    Frequent Visitor
    6 years ago

    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's avatar
      v-frfei-msft
      Community Support
      6 years ago

      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's avatar
        gazz9ar
        Frequent Visitor
        6 years ago


        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.