Measure for hide months without data

[marcopete]

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 for this: MedidaTiempoTotalIndMensual:=CALCULATE(SUM([IND]);FILTER('DIM TIEMPO'; 'DIM TIEMPO'[AÑO] >= YEAR(NOW()) - 1))

In this case until march without explicit filter.

I appreciate your help.

Regards.

[Anonymous]

@marcopete

1. I am assuming you have a date table with no gaps. 

2. I am also assuming you have a MonthNumber column in the Date table which is Month(Datetable[DateField])

3. Create a Measure called CurYrLastMonthNumber = Month(Lastdate(Sales[Date]))

     This will find the lastdate of from the sales table and get the corresponding Month Number for it.

4. Create a column in Date table called Show as

      ShowMonthsUpto = IF (
                     [MonthNumber] <= [CurYrLastMonthNumber] , 1, 0

                                )

5. Show will  have a value of 0 or 1 in the date table.

6. Assume you have a measure called [Sales] that calculates the sum of Sales . As per your requirment I am assuming your sales records contains future predicted sales.

7. Create a bar chart  where x-axis is Month and Y- axis is Sales measure.

8. In the visual level filter for the bar chart drag the field ShowMonthsUpto and set the filter condition show items when the value is 1.

9. You will then be able to see the Sales for the same period for both years. Months after the lastmonth in the sales for previous year will not be shown.

10. The above solution assumes you will always be showing for the current year and previous in the chart / report. 

Should you require further assistance , please do not hesitate to reply to this post.

If this works for you please accept it as a solution and also give kudos.

[marcopete · Accepted solution]

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.

[marcopete]

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 everyone again.

[kdejonge]

can you share the data? I am not completly clear how your measure works.

[Sean]

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]

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.