Solved: Dealing with different date in column and display ... - Page 2

Amardeep100115 · ‎03-09-2020

Hi,

i have historical data with filename - mm-dd-yy format also each file has date column which has date of the file got stored.

we are processing same thing every week and storing the files. i have grouped files by max date logic splitting the date from file name and have the expected data module.

now i wish to calculate customer count by month and when i select the any month the visule should display that month data and if no month is not selected then visual should display currant month data

Sample data

By using below DAX i have the result but not for currant month

2 www = IF(HASONEFILTER('Fiscal Calendar'[Month]),DISTINCTCOUNT('Active Headcount'[Persno]),CALCULATE(DISTINCTCOUNT('Active Headcount'[Persno]),FILTER('Fiscal Calendar','Fiscal Calendar'[Month Number]=MONTH(TODAY()))))

Measure 2 =

VAR _currentmonth=CALCULATE(MAX('Fiscal Calendar'[FiscalYear]),FILTER('Active Headcount',[ActiveHeadcount HR])) return

IF(HASONEVALUE('Fiscal Calendar'[Month]),[ActiveHeadcount HR],CALCULATE([ActiveHeadcount HR],FILTER('Fiscal Calendar','Fiscal Calendar'[FiscalYear]= _currentmonth)))

if there is no month selected then the card visula should display this value and get change on selection of other month too

AB

v-gizhi-msft · ‎03-15-2020

Hi,

Please try to change the original measure to this:

Measure = 
SWITCH (
    TRUE,
    SELECTEDVALUE ( 'Date Slicer'[Month] ) = BLANK ()
        && SELECTEDVALUE ( 'Date Slicer'[FiscalYear] ) <> BLANK (), CALCULATE (
        DISTINCTCOUNT ( Sheet1[Customer ID] ),
        FILTER (
            Sheet1,
            MONTH ( Sheet1[Month&Year] )
                = MONTH ( TODAY () ) - 1
                && Sheet1[Year]
                    = CALCULATE (
                        VALUE ( ( MAX ( 'Date Slicer'[Year] ) ) ),
                        FILTER (
                            'Date Slicer',
                            'Date Slicer'[FiscalYear] = SELECTEDVALUE ( 'Date Slicer'[FiscalYear] )
                        )
                    )
        )
    ),
    SELECTEDVALUE ( 'Date Slicer'[Month] ) = BLANK ()
        && SELECTEDVALUE ( 'Date Slicer'[FiscalYear] ) = BLANK (), CALCULATE (
        DISTINCTCOUNT ( Sheet1[Customer ID] ),
        FILTER (
            Sheet1,
            MONTH ( Sheet1[Month&Year] )
                = MONTH ( TODAY () ) - 1
                && Sheet1[Year] = YEAR ( TODAY () )
        )
    ),
    SELECTEDVALUE ( 'Date Slicer'[Month] ) <> BLANK ()
        && SELECTEDVALUE ( 'Date Slicer'[FiscalYear] ) <> BLANK (), CALCULATE (
        DISTINCTCOUNT ( Sheet1[Customer ID] ),
        FILTER (
            Sheet1,
            MONTH ( Sheet1[Month&Year] )
                = CALCULATE (
                    MONTH ( MAX ( 'Date Slicer'[Date] ) ),
                    FILTER (
                        'Date Slicer',
                        'Date Slicer'[Month] = SELECTEDVALUE ( 'Date Slicer'[Month] )
                    )
                )
                && Sheet1[Year]
                    = CALCULATE (
                        VALUE ( MAX ( 'Date Slicer'[Year] ) ),
                        FILTER (
                            'Date Slicer',
                            'Date Slicer'[FiscalYear] = SELECTEDVALUE ( 'Date Slicer'[FiscalYear] )
                        )
                    )
        )
    )
)

When you do not select any slicer, it shows the current year current month data:

Here is my changed pbix file:

pbix

Best Regards,

Giotto Zhi

View solution in original post