cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TCordeiro
Frequent Visitor

Relative time measures

Hello all,

 

I'm trying to do a SWITCH for Time Periods but my Calendar its not standard, some month don't start at Day 1 not do they end at the 30/31, they start at the first Monday of the week the month's begins.

i.e: March 2018, it starts at 26th February and ends at 1st April

feb_calendar.png

 

The switch I did is:

    Measure Period calculation = IF(COUNTROWS(VALUES('Time Period'[Index]))=1;
    SWITCH(VALUES('Time Period'[Name]);
                                    "YTD"; CALCULATE([Measure Selected];DATESYTD('Calendar'[Date]));
                                    "PY"; CALCULATE([Measure Selected];SAMEPERIODLASTYEAR('Calendar'[Date]));
                                    "MTD"; CALCULATE([Measure Selected];DATESMTD('Calendar'[Date]));
                                    "QTD"; CALCULATE([Measure Selected]; DATESQTD('Calendar'[Date]));
                                    "PYMTD"; CALCULATE([Measure Selected]; DATEADD(DATESMTD('Calendar'[Date]);-1;YEAR));
                                    "PYQTD"; CALCULATE([Measure Selected]; DATEADD(DATESQTD('Calendar'[Date]);-1;YEAR));
                                    "PYYTD"; CALCULATE([Measure Selected]; DATEADD(DATESYTD('Calendar'[Date]);-1;YEAR));
[Measure Selected]); 
blank()
)

But since the month aren't standard the result it returns is not what I want:


i.e: Using "MTD" measure i get these results

total_march.png

 

I need to add those 3 days in February plus the 1st of April since they "belong" to March so the total of Measure Period Calculation column shows the same result as Measure Selected column (green square)

PS: I do have a column in my Dim Calendar that provides the begin (Month_Start) and end (Month_End) of each months using these business logic.

 

 

If anyone could help, I really appreciate it.

 

Thanks,

Tiago

2 REPLIES 2
Super User I
Super User I

Re: Relative time measures

@TCordeiro -

You might want to take a look at Power Query to help you solve this problem; creating a custom period(s) calendar table. http://cjmendoza.yourweb.csuchico.edu/custom-calendar/create-a-custom-period-calendar-in-power-query... provides some context for what I purpose.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




TCordeiro
Frequent Visitor

Re: Relative time measures

Thanks for the reply @ChrisMendoza !

As I mentioned, I already have the PeriodStart and PeriodEnd columns in my Dim Calendar but I just can't seem to make the switch calculate using those periods...

Any thoughts?

Thanks in advance,
Tiago

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors