Default Filtering by Year and Month Abbrev

[cst_dev32]

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:

base on this:

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:

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?

[cst_dev32 · Accepted solution]

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

[v-yuta-msft]

Congratulations.

[affan]

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

[v-yuta-msft]

Hi cst_dev32，

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

Regards,

Jimmy Tao