Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
By using below DAX i have the result but not for currant month
Solved! Go to 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:
Here is my changed pbix file:
Best Regards,
Giotto Zhi
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
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.
Please suggest and guide
after below visual error i tried this CAR and some how not able to get correct reults
Visual error in amother filea fter using same containt of measure
Hi,
Please try to delete the relationship between two tables:
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:
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.
Here is the changed pbix file:
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
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])
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)
Due to lack of data in 2018 Feb in Sheet1, when choose FY-2018, it shows blank.
Here is the changed pbix file:
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
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:
Best Regards,
Giotto Zhi
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
Hi,
I advise you creating another case and you can still ping me at any time.
Thanks for your mark!
Best Regards,
Giotto Zhi
i have created new case, if possible please visit to below link and help me on this please
Thanks in advance
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.
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
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:
When you do not select any value in month slicer, it shows the current Year and Month's person count:
Here is my test pbix file:
Hope this can help.
Best Regards,
Giotto Zhi
@Amardeep100115 , not able to download the file. It gives size issue and did not give download option
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |