cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cst_dev32 Frequent Visitor
Frequent Visitor

Default Filtering by Year and Month Abbrev

Hi,

 

I'm have 2 situation here.

1st i'm trying to make some default filtering to PO Amount by Month Chart by Year_Number on slicer. The Year_Number is base on Dim_Date that was generate for 5 years from this year. The idea is the PO Amount by Month Chart must default showing the current year amount like this:

PO_Amount_by_Month.PNG

base on this:

Year_Slicer.PNG

 

this is my DAX this far:

 

IF(SELECTEDVALUE(Dim_Date[Year_Number]) = YEAR(TODAY());[PO_Amount_Daily];[PO_Amount_Daily])

 

my concern is when we get on 2019 or 2020, this dax should be SUM the all [PO_Amount_Daily] on the same Month_Abbrev, how to do this right?

 

 

the 2nd, i'm trying to make some default filtering to PO Amount by Date Chart by Year_Number on slicer and by PO Amount by Month Chart. Chart must default showing the current year and current month like this:

PO_Amount_by_Date.PNG

base on year slicer and month abbrev

 

this is the DAX:

 

 

DefaultMonthSelection2 = 
IF(
    COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Year_Number]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Year_Number]))) &&
    COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Month_Abbrev]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Month_Abbrev])))
    ;[PO_Amount_Daily];
    IF(
        SELECTEDVALUE(Dim_Date[Month_Abbrev]) = FORMAT(TODAY()-1;"MMM") && SELECTEDVALUE(Dim_Date[Year_Number]) = YEAR(TODAY()-1);
        [PO_Amount_Daily];BLANK()
    ))

How to do this all right?

 

1 ACCEPTED SOLUTION

Accepted Solutions
cst_dev32 Frequent Visitor
Frequent Visitor

Re: Default Filtering by Year and Month Abbrev

Hy @affan @v-yuta-msft

 

Thank's for reply to my question, but i figure it out by my self to solved this problem, FINALY after 5 Days!

 

here what i do to my case:

 

1st

 

I create calculated measure to display the default Amount by Month of this year if the slicer not being selected.

The slicer is filled by column named Dim_Date[Year_Number] 

here's the formula:

 

 

DefaultSelectionByMonth = 
IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Year_Number]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Year_Number])));
SUM([PO_Amount]);
CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Year_Number]);Dim_Date[Year_Number]=YEAR(TODAY()))))

this exacly what i neet to showing my data to PO Amount by Month Chart

 

 

2nd

 

Just like the PO Amount by Month Chart, the PO Amount by Date Chart is used calculated measure too, but the condition is by the columns Dim_Date[Year_Number] and Dim_Date[Month_Abbrev]

 

here's the formula:

 

DefaultSelectionByDate = 
IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Year_Number]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Year_Number])));
IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Month_Abbrev]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Month_Abbrev])));
SUM([PO_Amount]);
CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Month_Abbrev]);Dim_Date[Month_Abbrev]=FORMAT(TODAY();"MMM"))));
IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Month_Abbrev]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Month_Abbrev])));
CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Year_Number]);Dim_Date[Year_Number]=YEAR(TODAY())));
CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Month_Abbrev]);Dim_Date[Month_Abbrev]=FORMAT(TODAY();"MMM"));FILTER(ALL(Dim_Date[Year_Number]);Dim_Date[Year_Number]=YEAR(TODAY())))))

and this works!

 

All the calculated measure is used as value on stack chart. It's Also can be used in any visual that you want to show the default value before some visual or slicer clicked.

Maybe this can help others with the same issued.

 

Thank You All...

 

Best regards,

Adrin Pratama

 

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Default Filtering by Year and Month Abbrev

Hi cst_dev32

 

Could you share some sample data and explain more details about your requirement?

 

Regards,

Jimmy Tao

affan Established Member
Established Member

Re: Default Filtering by Year and Month Abbrev

Hi @cst_dev32

 

Can you please share the pbix file.  You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

 

Regards,

Affan

 

cst_dev32 Frequent Visitor
Frequent Visitor

Re: Default Filtering by Year and Month Abbrev

Hy @affan @v-yuta-msft

 

Thank's for reply to my question, but i figure it out by my self to solved this problem, FINALY after 5 Days!

 

here what i do to my case:

 

1st

 

I create calculated measure to display the default Amount by Month of this year if the slicer not being selected.

The slicer is filled by column named Dim_Date[Year_Number] 

here's the formula:

 

 

DefaultSelectionByMonth = 
IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Year_Number]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Year_Number])));
SUM([PO_Amount]);
CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Year_Number]);Dim_Date[Year_Number]=YEAR(TODAY()))))

this exacly what i neet to showing my data to PO Amount by Month Chart

 

 

2nd

 

Just like the PO Amount by Month Chart, the PO Amount by Date Chart is used calculated measure too, but the condition is by the columns Dim_Date[Year_Number] and Dim_Date[Month_Abbrev]

 

here's the formula:

 

DefaultSelectionByDate = 
IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Year_Number]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Year_Number])));
IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Month_Abbrev]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Month_Abbrev])));
SUM([PO_Amount]);
CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Month_Abbrev]);Dim_Date[Month_Abbrev]=FORMAT(TODAY();"MMM"))));
IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Month_Abbrev]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Month_Abbrev])));
CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Year_Number]);Dim_Date[Year_Number]=YEAR(TODAY())));
CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Month_Abbrev]);Dim_Date[Month_Abbrev]=FORMAT(TODAY();"MMM"));FILTER(ALL(Dim_Date[Year_Number]);Dim_Date[Year_Number]=YEAR(TODAY())))))

and this works!

 

All the calculated measure is used as value on stack chart. It's Also can be used in any visual that you want to show the default value before some visual or slicer clicked.

Maybe this can help others with the same issued.

 

Thank You All...

 

Best regards,

Adrin Pratama

 

View solution in original post

Community Support Team
Community Support Team

Re: Default Filtering by Year and Month Abbrev

Congratulations.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors