cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marcopete
Regular Visitor

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.

 

Sin título.png

 

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.

 

1 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.

View solution in original post

6 REPLIES 6
CheenuSing
Community Champion
Community Champion

@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.

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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.

View solution in original post

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
Microsoft
Microsoft

 

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

Sean
Community Champion
Community Champion

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

 

Show Only Active in CY Months.png

marcopete
Regular Visitor

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.