Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Amardeep100115
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

 

 

AB
1 ACCEPTED SOLUTION

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
Amardeep100115
Post Prodigy
Post Prodigy

Hi @v-gizhi-msft 

 

also could you please add sum function which i wish to use for another table

you may consider customer ID as sales amount and help me in this 

 

thanks in advance

 

AB
Amardeep100115
Post Prodigy
Post Prodigy

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

 

 

 

 

 

 

AB

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

 

 

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

 

AB

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

 

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

 

AB

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

 

@v-gizhi-msft

 

Hi Could you please help me with same function for previouse month.

 

AB

Hi @v-gizhi-msft 

i have been encountered with an error for some DAX which you shared 

 

i am trying use same DAX with same table.

 

Values shows 0 or(Blank)

 

Please guide

AB

I am marking a solution as it's solved mostly my issue but if i face any error in future on this query can i ping you in same thread .

Please advice
AB

Hi,

 

I advise you creating another case and you can still ping me at any time.

Thanks for your mark!

 

Best Regards,

Giotto Zhi

 

Hi @v-gizhi-msft 

 

i have created new case,  if possible please visit to below link and help me on this please 

 

New case link 

 

Thanks in advance

 

AB

Hi @v-gizhi-msft 

 

Superb,

 

Thank you so much for your greate help.

 

just one query 

 

like a date slicer do i need to create geographical table where i will have Region, country, City and Customer ID 

 

to achive geographical analysis ?

 

Please advice 

 

Thank you so much again for your great help.

 

 

 

AB

Hi,

 

If you want to achieve geographical analysis, i advise you creating a seperate slicer table to filter the fact table data.

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

Thanks!

 

Best Regards,

Giotto Zhi 

 

Amardeep100115
Post Prodigy
Post Prodigy

Please try below link 

 

 

Sample Data 

 

AB

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

 

amitchandak
Super User
Super User

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

 

Greg_Deckler
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.