cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Accepted Solutions
Microsoft
Microsoft

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

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

Thx Mate, Really helpful 🙂

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors