Forum Discussion

marcopete's avatar
marcopete
Regular Visitor
10 years ago
Solved

Measure for hide months without data

Hi.   Can you help me with that?    I have a chart with data from this year and previous year.   Attach image.     How I can show only the months with data for both years?    My dax ...
Need Help
Tips and Tricks
  • marcopete's avatar
    marcopete
    10 years ago

    Thanks everyone.

     

    This dax show me until i have the data, not based in data but in time and I resolved.

     

    CALCULATE(DIVIDE([Data1];[Data2]);FILTER('DIM TIEMPO';'DIM TIEMPO'[AÑO] >= YEAR(NOW()) -1);'DIM TIEMPO'[NUMERO DE MES] < MONTH(NOW()))

     

    Thanks again and all your replies are in my knowledge.

Sean's avatar
Sean
Community Champion
10 years ago

Hi marcopete We can do this by creating 2 Calculated COLUMNS in the Calendar Table

 

You first need to find the Last Transaction date (if you already have a Measure use the same formula)

But create a COLUMN in your calendar table

 

Then create a Show Month COLUMN (if your month column is text use version 1 if number use version 2) 

 

Last Transaction COLUMN = CALCULATE ( LASTDATE ( TableName[Transaction Date] ), ALL ( TableName ) )

Show Month COLUMN =
IF (
    MONTH ( 'Calendar'[Date] ) <= MONTH ( 'Calendar'[Last Transaction COLUMN] ),
    'Calendar'[Mo Num],
    "Don't Show"
)

Show Month COLUMN 2 =
IF (
    MONTH ( 'Calendar'[Date] ) <= MONTH ( 'Calendar'[Last Transaction COLUMN] ),
    'Calendar'[Month Number],
    100
)

 

Then you can use the Visual Level Filter either Show Month Column is not Don't Show OR Show Month Column 2 is not 100

As in the picture

 

  • marcopete's avatar
    marcopete
    Regular Visitor
    10 years ago

    Thanks, I'll try what you say.

     

    Just to know... it's possible with my actual dax? I'm trying with ISBLANK and FILTER commands.