PreviousQuarterYear, PreviousMonthYear, Previous MonthQuarter

[kalkhudary]

Hi everyone, 

I am looking into getting help in building my calender tabel for my dashboard. So, I did start by creating it but I failed to know the syntax for creating a " Previous YearMonth" and PreviousYearQuarter".

I currently created a Calender Table within my fields. 

I would appreciate if someone who help me in providing a syntax for a complete Calender table that  includes Date, Month, Year, Quarter, MonthYear, QuarterYear, MonthQuarter, PreviousQuarterYear, PreviousMonthYear, Previous MonthQuarter.

Thanks in advance.

or completing the attached calender table columns to include  " PreviousYearMonth" and PreviousYearQuarter", "PreviourMonthquarter".

[kalkhudary]

@v-henryk-mstf  Thank you Henry for sharing the custom table dax formula however I am looking precisely to get the previous yearmonth, previousyearquarter, previousmonthquarter in the calender table. Any idea what dax will pop these results for me in the calnder table. 

[v-henryk-mstf]

Hi @kalkhudary ,

You can try below formula to create custome calendar table:

Dates =
VAR BaseCalendar =
    CALENDAR ( DATE ( 2020, 1, 1 ), TODAY () )
RETURN
    GENERATE (
        BASECALENDAR,
        VAR BASEDATE = [DATE]
        VAR DayDate =
            DAY ( [DATE] )
        VAR YEARDATE =
            YEAR ( BASEDATE )
        VAR MONTHNUMBER =
            MONTH ( BASEDATE )
        VAR YEARMONTHNUMBER = YEARDATE * 12 + MONTHNUMBER - 1
        VAR FINANCIALQTR =
            SWITCH (
                TRUE (),
                MONTH ( [Date] ) IN { 7, 8, 9 }, 1,
                MONTH ( [Date] ) IN { 10, 11, 12 }, 2,
                MONTH ( [Date] ) IN { 1, 2, 3 }, 3,
                4
            )
        VAR FINANCIALYR =
            IF ( MONTH ( [Date] ) >= 7, YEARDATE, YEARDATE - 1 )
        RETURN
            ROW (
                "DAY", DayDate,
                "YEAR", YEARDATE,
                "MONTH NUMBER", MONTHNUMBER,
                "MONTH", FORMAT ( BASEDATE, "MMMM" ),
                "YEAR MONTH NUMBER", YEARMONTHNUMBER,
                "YEAR MONTH", FORMAT ( BASEDATE, "MMM YY" ),
                ------------------------------
                "Week Number", WEEKNUM ( [Date] ),
                "Week Ending",
                    [Date]
                        + ( 6 - ( WEEKDAY ( [Date], 3 ) ) ),
                "Day Name", FORMAT ( [Date], "DDDD" ),
                "CalendarQtr",
                    INT ( MONTH ( [Date] ) / 4 ) + 1,
                --
                "FinancialQtr", "FYQ" & FINANCIALQTR,
                "FinancialYr", "FY" & FINANCIALYR,
                "FinancialYrQtr", FINANCIALQTR & FINANCIALYR
            )
    )

For more, you can refer to below blog:

A Step By Step Guide To Creating A Custom Calendar Table In Power BI (Part 2) - Foresight BI - Achie...

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kalkhudary]

Thanks Vahid for sharing the link but I couldn't find anything that could help me build a calender table under the above conditions.

[VahidDM]

Hi @kalkhudary 

Use this tool to add a Date table to your report with 1 click:
https://www.sqlbi.com/tools/bravo-for-power-bi/

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube