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
Anonymous
Not applicable

Dynamic calculation based on Date selection

Hello Team,

 

I have a separate date table which has a column like distinct(datasource[CutoffDate]). then I use this column to get calculation based on the month as shown below. **( cut off date coming from my data source)

 

ZF_PM-2 = VAR CurrentMonth = MAX(Datetable2[MyDate])
VAR PreviousDate = FORMAT(DATE(YEAR(CurrentMonth), MONTH(CurrentMonth) - 2, 1),"yyyy-MM")
VAr Result =
CALCULATE(
SUM(FACT_IA_IIIO_XQ227[ProvAmt]),
FILTER(FACT_IA_IIIO_XQ227,
 
FORMAT(FACT_IA_IIIO_XQ227[Audit End(CutOffDate)],"yyyy-MM") = PreviousDate
)
)
Return
Result
 
I have this calculation from Current month till previous month -10.  right now my current month is July. I then use my date column as a slicer to filter my desired date range.
 
So if we use the slicer to take feb'19, my Current month should change to feb'19  and respectively other months. but it does not happen, the current month stays July no matter what is the selection on the slicer.
3 REPLIES 3
Greg_Deckler
Super User
Super User

What is your slicer based on, Datetable2[MyDate]?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

yes, the slicer is based on the myDate  table.

HI @Anonymous ,

Current power bi not support to create dynamic column/table based on slicer/filter. Please modify your formula to write a measure to achieve your requirement.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.