cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

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.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors