Power BI Percentage of Month passed

Question

I would like to create a calculated column or measure that shows the time elapsed in any given month. Preferably as a percentage.

I need to be able to compare productivity (rolling total) over a month's period between different months.So creating a percentage of time passed in a month would put every month on a level playing field.

Is there a way to do this?

Or is there a better way to compare productivity between 2 months on a rolling basis?

EDIT

I am graphing sales on a cumulative basis. Here is a picture of my graph to demonstrate.[][

Ideally I would like to be able to graph another person's sales on the same graph for a different month to compare.

The problem is each month is different and I don't think power bi allows much customization of the axes.

So I figured a potential solution would be to convert months to percentages of time passed, create two separate graphs and place them on top of each other to show the comparison of sales.

I think it depends on the context of "productivity". (you may need to exclude weekends for example). There are many time related DAX features, and if you describe your desired final goal (what kind of visualisation you're after), people may suggest a better / easier solution. — user5226582
@user5226582 I added some further explanation if that helps. Thanks! — GTA

user5226582 user5226582 · Accepted Answer · 2017-07-03T07:39:27

Using percentages doesn't sound right here: one person's "productivity" in February will appear lower than another person's productivity in March just because February has 3 less days.

Just use [Date].[Day].

To answer the original question (even though it shouldn't be used for this), month progress percentage calculated column:

MonthProgress% = 
var DaysinMonth = DAY( 
    IF(
        MONTH(MyTable[date]) = 12,
        DATE(YEAR(MyTable[date]) + 1,1,1),
        DATE(YEAR(MyTable[date]),  MONTH(MyTable[date]) + 1, 1)
    ) - 1
)
return MyTable[date].[Day]/DaysinMonth*100

Also check DAX functions PARALLELPERIOD and DATEADD.

Power BI Percentage of Month passed

2 Answers