Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey guys,
How to create a period to calculate a sum of a value between the 19th of the previous month and the 20th of the current month. I would like to be able to calculate my sum over this period and not from 1 to 31? I have this calendar for my time intelligence
Thank all !
Solved! Go to Solution.
@Anonymous -
Take a look at the attachment. I attempted a 'running total' measure then decided you'd probably want to use similar logic for a custom period so I added that as well. Hope it helps 😎!
Proud to be a Super User!
Hi @Anonymous ,
According to my understand , you want to calculate the sum in a specific Date frame(19th-20th), right?
You could add a MonthDay column in Date table using the following formula:
MonthDay =
MONTH ( [Date] ) * 100
+ DAY ( [Date] )
And then use the column directly to limit the range:
19th-20th =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[MonthDay]
>= ( MAX ( 'Table'[Month] ) - 1 ) * 100 + 19
&& 'Table'[MonthDay]
<= MAX ( 'Table'[Month] ) * 100 + 20
)
)
My final output looks like this:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @Anonymous ,
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Eyelyn Qin
Hello @CharlyExperteam ,
According to my understanding, you want to calculate the sum in a specific date frame (19o-20), right?
You can add a MonthDay column to the Date table using the following formula:
MonthDay =
MONTH ( [Date] ) * 100
+ DAY ( [Date] )
And then use the column directly to limit the range:
19th-20th =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[MonthDay]
>= ( MAX ( 'Table'[Month] ) - 1 ) * 100 + 19
&& 'Table'[MonthDay]
<= MAX ( 'Table'[Month] ) * 100 + 20
)
)
My final output looks like this:
Have I answered your question? Please mark my answer as a solution. Thanks a lot.
If not, load some insensitive data samples and expected output.
Best regards
Eyelyn Qin
Hi @Anonymous ,
According to my understand , you want to calculate the sum in a specific Date frame(19th-20th), right?
You could add a MonthDay column in Date table using the following formula:
MonthDay =
MONTH ( [Date] ) * 100
+ DAY ( [Date] )
And then use the column directly to limit the range:
19th-20th =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[MonthDay]
>= ( MAX ( 'Table'[Month] ) - 1 ) * 100 + 19
&& 'Table'[MonthDay]
<= MAX ( 'Table'[Month] ) * 100 + 20
)
)
My final output looks like this:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
@Anonymous -
Take a look at the attachment. I attempted a 'running total' measure then decided you'd probably want to use similar logic for a custom period so I added that as well. Hope it helps 😎!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |