Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
I have a problem. As a source, I have for each project, a start date and the duration in months of each project, so it is easy to get the end date of each project.
The question is that I need to get the total invoiced each month.
It is very clear in the example below: I need to get the totals for each month (green row).
Thank you very much!
Solved! Go to Solution.
Hi, @eomedes
Accorind to your sample data and requirements, I made a sample file to meet them.
First you need to create a single date table to use the column as matrix column.
And use the [PROYECT] column as matrix row, then create measure as matrix value to display your desired result.
Like this:
Table 2 = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))
Month = FORMAT([Date],"MMM")
Monthnum = MONTH('Table 2'[Date])
In order to sort the months of the text type correctly, you need to sort by column first.
Like this:
Amount =
IF (
SELECTEDVALUE ( 'Table 2'[Monthnum] )
>= MONTH ( SELECTEDVALUE ( 'Table'[START DATE] ) )
&& MAX ( 'Table 2'[Monthnum] ) <= MONTH ( SELECTEDVALUE ( 'Table'[END DATE] ) ),
MAX ( 'Table'[$/MONTH] )
)
AmountTotal =
IF (
SELECTEDVALUE ( 'Table 2'[Monthnum] )
>= MONTH ( SELECTEDVALUE ( 'Table'[START DATE] ) )
&& MAX ( 'Table 2'[Monthnum] ) <= MONTH ( SELECTEDVALUE ( 'Table'[END DATE] ) ),
MAX ( 'Table'[$/MONTH] ),
IF ( SELECTEDVALUE ( 'Table'[PROYECT] ) = BLANK (), SUMX ( 'Table', [Amount] ) )
)
Create [Amount] to display $/month then create [AmountTotal] using this measure for getting correct total.
Below is my sample.
Hi, @eomedes
Accorind to your sample data and requirements, I made a sample file to meet them.
First you need to create a single date table to use the column as matrix column.
And use the [PROYECT] column as matrix row, then create measure as matrix value to display your desired result.
Like this:
Table 2 = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))
Month = FORMAT([Date],"MMM")
Monthnum = MONTH('Table 2'[Date])
In order to sort the months of the text type correctly, you need to sort by column first.
Like this:
Amount =
IF (
SELECTEDVALUE ( 'Table 2'[Monthnum] )
>= MONTH ( SELECTEDVALUE ( 'Table'[START DATE] ) )
&& MAX ( 'Table 2'[Monthnum] ) <= MONTH ( SELECTEDVALUE ( 'Table'[END DATE] ) ),
MAX ( 'Table'[$/MONTH] )
)
AmountTotal =
IF (
SELECTEDVALUE ( 'Table 2'[Monthnum] )
>= MONTH ( SELECTEDVALUE ( 'Table'[START DATE] ) )
&& MAX ( 'Table 2'[Monthnum] ) <= MONTH ( SELECTEDVALUE ( 'Table'[END DATE] ) ),
MAX ( 'Table'[$/MONTH] ),
IF ( SELECTEDVALUE ( 'Table'[PROYECT] ) = BLANK (), SUMX ( 'Table', [Amount] ) )
)
Create [Amount] to display $/month then create [AmountTotal] using this measure for getting correct total.
Below is my sample.
Hello, thank you for your contribution, I was putting it into practice but when an activity has a period of one year e.g. Start date = 8 March 2020 and end date = 7 March 2021, in the matrix it does not fill in all the rows, it only puts the amount each March per year and the other fields blank. How could I fix this detail?
Best regards.
Hi,
Share some data to work with and show the expected result in a simple Table format.
Hi thanks for replying, I created the first formula:
I'd like to think it's because the formula is designed to show the data per month and not over the life of the contract.
Thank you for your help.
Best regards.
Hi,
I will not be able to help unless you share what i requested with you in my previous message.
Thank you very much!
It works perfectly!
I went for a different approach from @amitchandak . You can make the DAX much easier by shaping your data into a form power bi works well with:
In Power Query I started with your source data:
1) Create a list of months in a not loaded query:
let
Years = Table.FromList({2020..2022}, Splitter.SplitByNothing(), null,null, ExtraValues.Error),
LabelAsYear = Table.RenameColumns(Years,{{"Column1", "Year"}}),
AddMonth = Table.AddColumn(LabelAsYear, "Month", each {1..12}),
ShowMonths = Table.ExpandListColumn(AddMonth, "Month"),
CreateDate = Table.AddColumn(ShowMonths, "Date", each #date([Year],[Month],1))
in
CreateDate
2) Use this to generate a table with one row per project per month.
Effectively does a cross join so you have every project with every month and then filters out rows that aren't eligible.
let
Source = #"Source Data",
GetEndDate = Table.AddColumn(Source, "End Date", each Date.AddMonths([Start Date],[Months])),
CrossJoin = Table.AddColumn(GetEndDate, "Custom", each MonthList),
GetAllMonths = Table.ExpandTableColumn(CrossJoin, "Custom", {"Date"}, {"Date"}),
ChangedType = Table.TransformColumnTypes(GetAllMonths,{{"Date", type date}, {"End Date", type date}}),
AddEligbilityCheck = Table.AddColumn(ChangedType, "Eligible", each
if [Date] >= [Start Date] and [Date] <= [End Date]
then 1
else 0),
FilterToEligble = Table.SelectRows(AddEligbilityCheck, each ([Eligible] = 1)),
RemovedOtherColumns = Table.SelectColumns(FilterToEligble,{"Date", "$/Month", "Project"})
in
RemovedOtherColumns
3) In your data model it's easier if you add a basic calendar table.
4) Because your data is now at the month granularity you can get your results just using normal power bi visuals and little/no DAX:
Solution available here .
@eomedes , refer if one of my two blogs can help
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
194 | |
111 | |
105 | |
79 | |
71 |