Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rekaush
New Member

Want to find no of Month from two different date

Experts,

 

I am new in Power BI -  trying to explain my prob I am facing,

 

I wanted to bifurcate month from date Rate. i.e

Project start date - 1st Jan'18 to 31st Dec' 19 - so # of month i want in 2018 column = 12 & 2019 = 12

 

so on each row of the table, I want no of Month in Column wise (in Year formate)

 

Thanks 

Kaush

 

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

Hi @rekaush

 

You could try the following calculated column.  Just change the VAR myYear variable to 2019 for the 2019 column

 

2018 Month = 
VAR myYear = 2018
VAR Months = 
        SUMMARIZE(
            ADDCOLUMNS(
                CALENDAR( DATE(myYear,1,1), DATE(myYear,12,1) ),
                    "Month",
                    DATE(
                        YEAR([Date]),
                        MONTH([Date]),
                        1)
                        )
                       ,[Month])
RETURN
    COUNTROWS(
            FILTER(
                Months,
                [Month]>=EARLIER(Projects[Project Start Date]) &&
                    [Month] <=EARLIER('Projects'[Project End Date])
                     ))

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Phil_Seamark
Employee
Employee

Hi @rekaush

 

You could try the following calculated column.  Just change the VAR myYear variable to 2019 for the 2019 column

 

2018 Month = 
VAR myYear = 2018
VAR Months = 
        SUMMARIZE(
            ADDCOLUMNS(
                CALENDAR( DATE(myYear,1,1), DATE(myYear,12,1) ),
                    "Month",
                    DATE(
                        YEAR([Date]),
                        MONTH([Date]),
                        1)
                        )
                       ,[Month])
RETURN
    COUNTROWS(
            FILTER(
                Months,
                [Month]>=EARLIER(Projects[Project Start Date]) &&
                    [Month] <=EARLIER('Projects'[Project End Date])
                     ))

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thx Mate, Really helpful 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.