cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Dealing with different date in column and display current month values

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)))
 
Result if no month selected.JPG
if there is no month selected then the card visula should display this value and get change on selection of other month too
if there is no month selected then the card visula should display this value and get change on selection of other month tooif there is no month selected then the card visula should display this value and get change on selection of other month too

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Dealing with different date in column and display current month values

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:

100.PNG

Here is my changed pbix file:

pbix 

 

Best Regards,

Giotto Zhi

 

View solution in original post

18 REPLIES 18
Highlighted
Super User IV
Super User IV

Re: Dealing with different date in column and display current month values

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: Dealing with different date in column and display current month values

@Amardeep100115 , not able to download the file. It gives size issue and did not give download option

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Post Prodigy
Post Prodigy

Re: Dealing with different date in column and display current month values

Please try below link 

 

 

Sample Data 

 

Highlighted
Community Support
Community Support

Re: Dealing with different date in column and display current month values

Hi,

 

Please try this measure:

Measure = 
IF (
    SELECTEDVALUE ( Sheet1[Month] ) = BLANK (),
    CALCULATE (
        DISTINCTCOUNT ( Sheet1[Customer ID] ),
        FILTER (
            Sheet1,
            MONTH ( Sheet1[Month&Year] ) = MONTH ( TODAY () )
                && Sheet1[Year] = YEAR ( TODAY () )
        )
    ),
    DISTINCTCOUNT ( Sheet1[Customer ID] )
)

Because your sample has no value in 2020 March(today), so i change its today's month to Feb to test, like 'MONTH(TODAY())-1':

So when you select month in slicer, it shows:

30.PNG

When you do not select any value in month slicer, it shows the current Year and Month's person count:

31.PNG

Here is my test pbix file:

pbix 

Hope this can help.

 

Best Regards,

Giotto Zhi

 

Highlighted
Post Prodigy
Post Prodigy

Re: Dealing with different date in column and display current month values

Hi @v-gizhi-msft 

 

 

Don't know why but it work in yout file and not in my file, after opening your file it's shows below error and after i click the file got open and i can see the results and it work in your file after i add fiscal calender table in it. but dont know why it not working in my power BI file. i have reinstall my power BI.

 

Also there one request in shared BI file if shows data for FY 2020 but i also wish to do same with earlier FY like FY2019, FY2018 etc.

when i select FY2019 then the visual should display Sept 2019 value in card. 

 

i have uploaded reworked shared file.

 

Sample Data revised 

 

Please suggest and guide 

 

 

Casdawdcapture.JPG

after below visual error i tried this CAR and some how not able to get correct reults

 

Measure =
VAR _currantmonth = CALCULATE(SELECTEDVALUE('Fiscal Calendar'[Month]),FILTER('ActoveHeadcount',DISTINCTCOUNT(ActoveHeadcount[Persno]))) return IF(
SELECTEDVALUE ( 'Fiscal Calendar'[Month] )=BLANK(),[HC],
CALCULATE (
DISTINCTCOUNT ( ActoveHeadcount[Persno] ),
FILTER (
'Fiscal Calendar', 'Fiscal Calendar'[Month]=_currantmonth )
)
 
)

 

 

 

Visual error in amother filea fter  using same containt of measure 

Visual error.JPGVisual error 2.JPG

 

 

 

 

 

 

Highlighted
Community Support
Community Support

Re: Dealing with different date in column and display current month values

Hi,

 

Please try to delete the relationship between two tables:

70.PNG

Then try this measure:

Measure = 
IF (
    SELECTEDVALUE ( 'Fiscal Calendar'[Month] ) = BLANK (),
    CALCULATE (
        DISTINCTCOUNT ( Sheet1[Customer ID] ),
        FILTER (
            Sheet1,
            MONTH ( Sheet1[Month&Year] )
                = MONTH ( TODAY () ) - 1
                && Sheet1[Year] = YEAR ( TODAY () )
        )
    ),
    CALCULATE (
        DISTINCTCOUNT ( Sheet1[Customer ID] ),
        FILTER (
            Sheet1,
            MONTH ( Sheet1[Month&Year] )
                = CALCULATE (
                    MONTH ( MAX ( 'Fiscal Calendar'[Date] ) ),
                    FILTER (
                        'Fiscal Calendar',
                        'Fiscal Calendar'[Month] = SELECTEDVALUE ( 'Fiscal Calendar'[Month] )
                    )
                )
                && Sheet1[Year]
                    = CALCULATE (
                        VALUE(MAX ( 'Fiscal Calendar'[Year] )),
                        FILTER (
                            'Fiscal Calendar',
                            'Fiscal Calendar'[FiscalYear] = SELECTEDVALUE ( 'Fiscal Calendar'[FiscalYear] )
                        )
                    )
        )
    )
)

When you select Month and FiscalYear in slicers, it shows:

72.PNG

When you only select FiscalYear in slicer, it shows the current year and month's data:

Tips: In this case there is no data in March 2020, so i changed its MONTH(TODAY()) to Feb.

71.PNG

Here is the changed pbix file:

pbix 

 

Best Regards,

Giotto Zhi

 

 

Highlighted
Post Prodigy
Post Prodigy

Re: Dealing with different date in column and display current month values

Wow, It's work.
Thank you so much my super hero.
just one little request. might be you missed my 2nd request in last message " Also wish to do same with earlier FY like FY2019, FY2018 etc.".

Is it possible to help me on this in same DAX.

 

Query: please let me know if i use same DAX for another table with same request do i need delete the relashionship for those tables as well. 

please guide

 

Please

Thanks once again my super hero

 

Highlighted
Community Support
Community Support

Re: Dealing with different date in column and display current month values

Hi,

 

You do not have to deleting the relationships and i have another better way:

Please try to create a date slicer table based on Fiscal Calendar table:(do not set any other relationships)

Date Slicer = SELECTCOLUMNS('Fiscal Calendar',"FiscalYear",'Fiscal Calendar'[FiscalYear],"Month",'Fiscal Calendar'[Month],"Date",'Fiscal Calendar'[Date],"Year",'Fiscal Calendar'[Year])

2.PNG

Choose [FiscalYear] and [Month] from date slicer table as slicers.

Then try this measure:

Measure = 
IF (
    SELECTEDVALUE ( 'Date Slicer'[Month] ) = 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] )
                        )
                    )
        )
    ),
    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 month in [Month] slicer and choose other year(not 2020) in [Year] slicer, it shows:

Tips: In this case i defined Feb as the current month(actually is Mar)

3.PNG

Due to lack of data in 2018 Feb in Sheet1, when choose FY-2018, it shows blank.

1.PNG

Here is the changed pbix file:

pbix 

If my answer has solved your issue, please mark it as a solution for others to see.

Thanks!

 

Best Regards,

Giotto Zhi

 

Highlighted
Post Prodigy
Post Prodigy

Re: Dealing with different date in column and display current month values

Hi @v-gizhi-msft ,

 

You are rocking, just amazing.

Thank you !

But there is small thing which is missing.

when i not selected any option from slicers the measure should be show Currant month value. rest measure is working with fiscal year and month as well.

 

Could you please fix this? Please 

Thanks in advance

 

Error.jpg

 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors